What’s the Difference Between Where and Having in SQL?

A really quick post for you all today over the differences between Where and Having in SQL. This has been a question in a lot of interviews, but no one seems to know why and when to use each of these clauses.

Where

The where clause is used to filter based on criteria other than aggregates. So say for example you wanted to restrict the results to a single person, “Jacob Saylor”, you would do something similar to the following :

SELECT FullName, SUM(Deposit) as Balance
FROM Accounts
WHERE FullName = 'Jacob Saylor'
GROUP BY FullName

A really straight forward example showing the most common use of both the where and the group by.

Having

The having clause is used to filter based on aggregate functions in conjunction with a group by clause. It can also be used with just a group by clause as well (See the Following example).

SELECT FullName, SUM(Deposit) as Balance
FROM Accounts
GROUP BY FullName
HAVING FullName = 'Jacob Saylor'

This is used a lot less often than the where due to a performance hit. See Performance below.

Where and Having Working Together

So now that we have seen Where and Having used in their own context, let’s use them together. In the following example we use where to restrict all deposits greater than $5, and where the sum of these deposits are greater than $25.

SELECT FullName,SUM(Deposit) AS DepositsGT5
FROM Accounts
WHERE Deposit > 5
GROUP BY FullName
HAVING SUM(Deposit) > 25

This will be the far best way to use them together, filtering content with the Where and aggregates with the Having.

Performance

By SQL Standard, a Where clause will restrict the set before returning the result set, while the Having clause will restrict the results after it is returned. This means when you don’t have to use a having clause, don’t.