Editorials

WHERE vs. HAVING

Today I wanted to talk about the difference between the WHERE clause and the HAVING clause in a SQL query. They both filter the data that is returned from the query, but the work quite differently.

The WHERE clause is completely independent of the HAVING clause. If you specify a where clause in your query, every record returned from the SELECT statement is evaluated by the definition of the WHERE clause, and only those records that pass the criteria are returned

The HAVING clause works quite differently. In fact, you can not specify a HAVING clause unless you also specify a GROUP BY clause. When records are grouped together based on the GROUP BY criteria, the results are then filtered by the HAVING clause at the group level.

So, the WHERE clause filters individual rows, and the HAVING clause filters individual groups, which are summary records.

You can have both a WHERE clause and a HAVING clause in the same query, as long as the query also has a GROUP BY clause. You can also have just a HAVING clause without any WHERE clause. They are completely independent.

These two clauses are probably the most significant implementations in the SQL language, and understanding the difference, and when to apply each is essential to good query development.

Cheers,

Ben

Facebooktwittergoogle_plusredditpinterestlinkedinmail
  • Marc L

    Quick question: is there an irreducible difference between

    select … from … group by … having …

    and

    select * from (select … from … group by … ) where …