What is the Difference between WHERE and HAVING Clauses?

In this article, learn when to use WHERE and HAVING. Both perform similar functions, but for different purposes!

All the examples for this article are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. You can get started using these free tools using my Guide, Getting Started Using SQL Server.

How Do Where and Having Differ?

When working with more advanced SQL, it can be unclear when it makes sense to use a WHERE versus a HAVING clause.

Though it appears that both clauses do the same thing, they do it in different ways. In fact, their functions complement each other.

• A WHERE clause is used to filter records from a result. The filter occurs before any groupings are made.
• A HAVING clause is used to filter values from a group.

Before we go any further, let’s review the format of an SQL statement. It is:

SELECT
FROM
WHERE
GROUP BY
HAVING

To help keep things straight, I like to think of the order of execution of SQL statements as from top to bottom. That means the WHERE clause is first applied to the result and then, the remaining rows summarized according to the GROUP BY.

WHERE Clause

The WHERE clause is used to filter rows from results. For instance:

SELECT  COUNT(SalesOrderID)
FROM    Sales.SalesOrderDetail

Returns 121,317 as the count, whereas, the query...

SELECT  COUNT(SalesOrderID)
FROM    Sales.SalesOrderDetail
WHERE   UnitPrice > 200

...returns 48,159 as the count. This is because the WHERE clause filters out the 73,158 SalesOrderDetails whose UnitPrice is less than or equal to 200 from the results.

HAVING Clause

The HAVING clause is used to filter values in a GROUP BY. You can use them to filter out groups such as:

SELECT  SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM    Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING  SalesOrderID > 50000

But their true power lies in their ability to compare and filter based on aggregate function results. For instance, you can select all orders totalling more than $10,000.

SELECT  SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM    Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING  SUM(UnitPrice * OrderQty) > 10000

Since the WHERE clause’s visibility is one row at a time, there isn’t a way for it to evaluate the SUM across all SalesOrderIDs. The HAVING clause is evaluated after the grouping is created.

Combining the Two: WHERE and HAVING

When SQL statements have both a WHERE clause and HAVING clause, keep in mind the WHERE clause is applied first, then the results grouped, and finally the groups filtered according to the HAVING clause.

In many cases, you can place the WHERE condition in the HAVING clause, such as:

SELECT  SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM    Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING  SUM(UnitPrice * OrderQty) > 10000 
         AND SalesOrderID > 50000

versus:

SELECT  SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM    Sales.SalesOrderDetail
WHERE   SalesOrderID > 50000
GROUP BY SalesOrderID
HAVING  SUM(UnitPrice * OrderQty) > 10000

If you can put condition from the where clause in the having clause, then why even worry about the WHERE? Can I just use this query?

SELECT  SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM    Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING  SUM(UnitPrice * OrderQty) > 10000 AND LineTotal > 10

Actually, that query generates an error. The column LineTotal is not part of the group by field list nor the result of an aggregate total.

To be valid, the having clause can only compare results of aggregated functions or column part of the group by.

To be valid, the query has to be rewritten as:

SELECT  SalesOrderID,
         SUM(UnitPrice * OrderQty) AS TotalPrice
FROM    Sales.SalesOrderDetail
WHERE   LineTotal > 100
GROUP BY SalesOrderID
HAVING  SUM(UnitPrice * OrderQty) > 10000

To summarize the difference between WHERE and HAVING:

• WHERE is used to filter records before any groupings take place.
• HAVING is used to filter values after they have been groups. Only columns or expression in the group can be included in the HAVING clause’s conditions.

The post What is the difference between WHERE and HAVING clauses? appeared first on Essential SQL.