What is the difference between HAVING and WHERE in SQL?
What is the difference between
WHERE in an
SQL SELECT statement?
EDIT: I have marked Steven's answer as the correct one as it contained the key bit of information on the link:
GROUP BYis not used,
HAVINGbehaves like a
The situation I had seen the
WHERE in did not have
GROUP BY and is where my confusion started. Of course, until you know this you can't specify it in the question.
HAVING: is used to check conditions after the aggregation takes place.
WHERE: is used to check conditions before the aggregation takes place.
select City, CNT=Count(1) From Address Where State = 'MA' Group By City
Gives you a table of all cities in MA and the number of addresses in each city.
select City, CNT=Count(1) From Address Where State = 'MA' Group By City Having Count(1)>5
Gives you a table of cities in MA with more than 5 addresses and the number of addresses in each city.
Read more... Read less...
Number one difference for me: if
HAVING was removed from the SQL language then life would go on more or less as before. Certainly, a minority queries would need to be rewritten using a derived table, CTE, etc but they would arguably be easier to understand and maintain as a result. Maybe vendors' optimizer code would need to be rewritten to account for this, again an opportunity for improvement within the industry.
Now consider for a moment removing
WHERE from the language. This time the majority of queries in existence would need to be rewritten without an obvious alternative construct. Coders would have to get creative e.g. inner join to a table known to contain exactly one row (e.g.
DUAL in Oracle) using the
ON clause to simulate the prior
WHERE clause. Such constructions would be contrived; it would be obvious there was something was missing from the language and the situation would be worse as a result.
TL;DR we could lose
HAVING tomorrow and things would be no worse, possibly better, but the same cannot be said of
From the answers here, it seems that many folk don't realize that a
HAVING clause may be used without a
GROUP BY clause. In this case, the
HAVING clause is applied to the entire table expression and requires that only constants appear in the
SELECT clause. Typically the
HAVING clause will involve aggregates.
This is more useful than it sounds. For example, consider this query to test whether the
name column is unique for all values in
SELECT 1 AS result FROM T HAVING COUNT( DISTINCT name ) = COUNT( name );
There are only two possible results: if the
HAVING clause is true then the result with be a single row containing the value
1, otherwise the result will be the empty set.
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.
Check out this w3schools link for more information
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
A query such as this:
SELECT column_name, COUNT( column_name ) AS column_name_tally FROM table_name WHERE column_name < 3 GROUP BY column_name HAVING COUNT( column_name ) >= 3;
...may be rewritten using a derived table (and omitting the
HAVING) like this:
SELECT column_name, column_name_tally FROM ( SELECT column_name, COUNT(column_name) AS column_name_tally FROM table_name WHERE column_name < 3 GROUP BY column_name ) pointless_range_variable_required_here WHERE column_name_tally >= 3;
The difference between the two is in the relationship to the GROUP BY clause:
WHERE comes before GROUP BY; SQL evaluates the WHERE clause before it groups records.
HAVING comes after GROUP BY; SQL evaluates HAVING after it groups records.
HAVING is used when you are using an aggregate such as
SELECT edc_country, COUNT(*) FROM Ed_Centers GROUP BY edc_country HAVING COUNT(*) > 1 ORDER BY edc_country;
WHERE is applied as a limitation on the set returned by SQL; it uses SQL's built-in set oeprations and indexes and therefore is the fastest way to filter result sets. Always use WHERE whenever possible.
HAVING is necessary for some aggregate filters. It filters the query AFTER sql has retrieved, assembled, and sorted the results. Therefore, it is much slower than WHERE and should be avoided except in those situations that require it.
SQL Server will let you get away with using HAVING even when WHERE would be much faster. Don't do it.