The difference between having and where in SQL

stay SQL in , General addition, deletion, query and modification statements use WHERE Keywords to retrieve special fields , But when SELECT Used in query statements GROUP BY For grouping queries , For conditional filtering of grouped results, you need HAVING key word .

Simply speaking , Namely WHERE Used before grouping ,HAVING Used after grouping ！

for example , Find the average score of classes with class number less than four

SELECT class, AVG(score) 
FROM test 
WHERE class<4 
GROUP BY class;


SELECT class, AVG(score) 
FROM test 
GROUP BY class 
HAVING class<4;

Both of the above expressions are correct , But the former is to filter first and then group , The latter is to group first and then filter .

Of course , This example is just one by one , In many cases, you must filter the grouped data , that HAVING It'll come in handy .