Where and Having distinction

Similarities and differences between Where and Having

(1) where is filtered before the query returns the result.

(2) having filters the results after the query returns them

(3) the reason for adding the HAVING clause to SQL is that the where keyword cannot be used with the aggregate function, and the having clause is often used with group by to filter the grouped data

Test table

Mysql > select * from t_order +-+ | emp_no | dept_no | from_date | to_date | +-+ | 22744 | d006 | 1986-12-01 | 9999 -01-01 | 24007 | d005 | 1986-12-01 | 9999-01 | 30970 | d005 | 1986-12-01 | 2017-03-29 | 31112 | d002 | 1986-12-01 | 1993-12-01 | 1993-12-01 | d005 | d008 | 1986-12-01 | 9999-01 | 1992-05-27 | 48317 | d008 | 1986-12-01 | 2017-01-11 | 49667 | d007 | 1986-12-01 | 9999 | -01-01 | | 50449 | d005 | 1986-12-01 | 9999-01 | | 10004 | d004 | 1986-12-01 | 9999-01-01 | +-+ 10 rows in set (0.00 sec)

After Where, if the alias aaa of column emp_no is used to filter in advance, it will report that the wrong column does not exist. This is because the where sentence first filters the table before starting the query result.

Mysql > select emp_no as aaa from t_order where emp_no=22744;+-+ | aaa | +-+ | 22744 | +-+ 1 row in set (0.00 sec) mysql > select emp_no as aaa from t_order where aaa=22744;ERROR 1054 (42S22): Unknown column 'aaa' in' where clause'

Having can be followed by the original name or alias aaa of the column emp_no, or group by, and then filter the grouped aggregate function column

Mysql > select emp_no as aaa from t_order having emp_no=22744;+-+ | aaa | +-+ | 22744 | +-+ 1 row in set (sec) mysql > select emp_no as aaa from t_order having aaa=22744 +-+ | aaa | +-+ | 22744 | +-+ 1 row in set (sec) mysql > select dept_no,min (emp_no) aaa from t_order group by dept_no having aaa=31112 +-+-+ | dept_no | aaa | +-+-+ | d002 | 31112 | +-+-+ 1 row in set (sec)