Difference between HAVING and WHERE clause in SQL

sql where-clause having-clause
user909058 picture user909058 · Sep 12, 2011 · Viewed 14.4k times · Source 
SELECT column_name, aggregate_function(column_name) 
FROM table_name 
WHERE column_name operator value 
GROUP BY column_name 
HAVING aggregate_function(column_name) operator value

What is the difference between having and where

Answer

Ben Lee picture Ben Lee · Sep 12, 2011

where filters on the select ... from

having filters on the aggregate results from the group by ...

So, looking at your example again:

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

Here, WHERE column_name operator value says "Return results for table_name where 'column_name operator value' is true".

Only after all the results from these conditions are found, it groups by column_name.

Then HAVING aggregate_function(column_name) operator value says "For the resulting aggregate groups, run 'aggregate_function(column_name)' and return only results where 'aggregate_function(column_name) operator value' is true."

Related questions

WHERE vs HAVING

Why do you need to place columns you create yourself (for example select 1 as "number") after HAVING and not WHERE in MySQL? And are there any downsides instead of doing WHERE 1 (writing the whole definition instead of a column name)?

Read More
SQL JOIN - WHERE clause vs. ON clause

After reading it, this is not a duplicate of Explicit vs Implicit SQL Joins. The answer may be related (or even the same) but the question is different. What is the difference and what should go in each? If I …

Read More
How to write a SQL DELETE statement with a SELECT statement in the WHERE clause?

Database: Sybase Advantage 11 On my quest to normalize data, I am trying to delete the results I get from this SELECT statement: SELECT tableA.entitynum FROM tableA q INNER JOIN tableB u on (u.qlabel = q.entityrole AND u.fieldnum = …

Read More