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
This would select data row by row basis.
The having clause works on aggregated data.
For example, output of below query
When we apply having in above query, we get
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
Attention reader! Don’t stop learning now. Get hold of all the important CS Theory concepts for SDE interviews with the CS Theory Course at a student-friendly price and become industry ready.
- Difference between Having clause and Group by clause
- SQL query using COUNT and HAVING clause
- Difference between Where and Having Clause in SQL
- Having clause in MS SQL Server
- SQL | Distinct Clause
- SQL | WHERE Clause
- SQL | SELECT TOP Clause
- SQL | Union Clause
- SQL | WITH clause
- Combining aggregate and non-aggregate values in SQL using Joins and Over clause
- Difference between order by and group by clause in SQL
- Difference between From and Where Clause in SQL
- Distinct clause in MS SQL Server
- Where clause in MS SQL Server
- Group by clause in MS SQL Server
- MySQL | PARTITION BY Clause
- Update clause in Cassandra
- SQL | Difference between functions and stored procedures in PL/SQL
- Difference between T-SQL and PL-SQL
- Difference between SQL and T-SQL