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 SQL 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 is 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 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 of the count, whereas, the query
SELECT COUNT(SalesOrderID) FROM Sales.SalesOrderDetail WHERE UnitPrice > 200
Returns 48,159 as the count. This is because of 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 totaling 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 SalesOrderID’s. 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.
Rewrite the query as:
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
WHERE LineTotal > 100
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 10000
In conclusion, the difference between WHERE and HAVING are:
- 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 expressions in the group can be included in the HAVING clause’s conditions…
Leave a Reply