Having vs Where Clause?
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, |
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
|
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.
Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above
Recommended Posts:
- SQL | WITH clause
- SQL | WHERE Clause
- SQL | SELECT TOP Clause
- SQL | Union Clause
- SQL | Distinct Clause
- MySQL | PARTITION BY Clause
- Combining aggregate and non-aggregate values in SQL using Joins and Over clause
- Neo4j Introduction
- SELECT INTO statement in SQL
- Extendible Hashing | A Dynamic approach to DBMS
- LOB Locator and LOB Value
- Basic operations and Working of LOB
- Cascadeless in DBMS
- Weak Entity Set in ER diagrams
