Having vs Where Clause:-
| Where | Having |
|---|---|
| WHERE clause can be used with SELECT, INSERT, and UPDATE statements | HAVING can be used only with SELECT statement. |
| WHERE filters rows before aggregation (GROUP BY) | HAVING filter groups after aggregations are performed. |
| Aggregate function cannot be used in WHERE clause unless it is in a subquery contained in HAVING clause | Aggregate functions can be used in HAVING clause |
| WHERE clause is used for filtering rows and it applies on each and every row | HAVING Clause is used to filter group in Sql |
| WHERE clause is used before GROUP BY clause | HAVING Clause is used after Group By Clause |
Example Of Having and Where Clause:-
Query
SELECT Student,Score FROM Marks WHERE Score >=40 |
|---|
Student Course Score
a c1 40
a c2 50
b c3 60
d c1 70
e c2 80
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