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