What is the difference between HAVING and WHERE in SQL?

Question

HAVING: is used to check conditions after the aggregation takes place.
WHERE: is used to check conditions before the aggregation takes place.

This code:

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.

This code:

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.

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

Source

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 WHERE.

---

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 T:

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

Syntax:

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.

References

• SQLite SELECT Statement Syntax/Railroad Diagram

• Informix SELECT Statement Syntax/Railroad Diagram

HAVING is used when you are using an aggregate such as GROUP BY.

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.