Understand the difference between where and having in sql statements

HAVING first groups and then filters the records, WHERE first filters the records before aggregating. That is to say, it acts before the GROUP BY clause and the HAVING clause; and the HAVING clause filters the group records after aggregation.
The objects used are different. The WHERE clause acts on tables and views, and the HAVING clause acts on groups. WHERE selects the input rows before grouping and aggregation calculations (therefore, it controls which rows enter the aggregation calculation), while HAVING selects the grouped rows after grouping and aggregation. Therefore, the WHERE clause cannot contain an aggregate function ; because it makes no sense to try to use an aggregate function to determine which rows are input to the aggregation operation. In contrast, the HAVING clause always contains aggregate functions. (Strictly speaking, you could write the HAVING clause without using aggregation, but doing so would be a waste of time. The same condition could be used more efficiently in the WHERE stage.) For example:
select * from tablename where id > 1; and
select * from tablename having id > 1
There is no difference between the query results. It is not recommended to use having
To summarize in two sentences:
The WHERE statement comes before the GROUPBY statement; SQL will calculate the WHERE statement before grouping.
HAVING statement follows the GROUPBY statement; SQL will calculate the HAVING statement after grouping.

The above is the detailed content of Understand the difference between where and having in sql statements. For more information, please follow other related articles on the PHP Chinese website!