The difference between the having and where clause in SQL is that the where clause cannot be used with aggregates, but the having clause can.
The where clause works on row’s data, not on aggregated data. Let us consider below table ‘Marks’.
Student      Course     Score
a               c1            40
a         c2       50
b         c3       60
d         c1       70
e         c2       80
Consider the query
|
SELECT Student, Score FROM Marks WHERE Score >=40
|
This would select data row by row basis.
The having clause works on aggregated data.
For example, output of below query
|
SELECT Student, SUM (score) AS
total FROM Marks GROUP BY Student
|
Student    Total
a             90
b        60
d        70
e        80
When we apply having in above query, we get
|
SELECT Student, SUM (score) AS
total FROM Marks GROUP BY Student
HAVING total > 70
|
Student    Total
a             90
e        80
Note: It is not a predefined rule but in a good number of the SQL queries, we use WHERE prior to GROUP BY and HAVING after GROUP BY. The Where clause acts as a pre filter where as Having as a post filter.