Having vs Where Clause?

2.1

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.

Source: http://qa.geeksforgeeks.org/599/cannot-where-clause-with-aggregate-functions-like-having-sql

Please write comments if you find anything incorrect, or you want to share more information about the topic discussed above

GATE CS Corner    Company Wise Coding Practice

Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.

Recommended Posts:



2.1 Average Difficulty : 2.1/5.0
Based on 16 vote(s)










Writing code in comment? Please use ide.geeksforgeeks.org, generate link and share the link here.