Both clauses in SQL statements are filtering a specific output, but there’s a significant difference. Here’s what Wikipedia tells us:
A HAVING clause in SQL specifies that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions.
Aggregate Functions are the following:
- AVG
- COUNT
- FIRST
- LAST
- MAX
- MIN
- SUM
So HAVING clauses can only be used if you are using an aggregate function with a GROUP BY clause. I’ve set up a small table on MS SQL Server to try this out:
Name | Wins |
Toby | 23 |
Toby | 11 |
Lisa | 16 |
John | 4 |
John | 7 |
Here is our SELECT statement for this table and its output:
SELECT Name, SUM(Wins) FROM WinRecords GROUP BY Name
Name | Wins |
John | 11 |
Lisa | 16 |
Toby | 34 |
This grouped the results by the names of our entries. But what if we want to display entries with more than 10 wins? We can try using a WHERE clause:
SELECT Name, SUM(Wins) FROM WinRecords WHERE Wins > 10 GROUP BY Name
Name | Wins |
Lisa | 16 |
Toby | 34 |
That didn’t work as we expected it to. John has a total of 11 wins, so he should be displayed too. That’s because the WHERE clause runs before the aggregate functions, while HAVING runs after the aggregate values have been calculated.
Here’s the query with HAVING:
SELECT Name, SUM(Wins) FROM WinRecords GROUP BY Name HAVING SUM(Wins) > 10
Name | Wins |
John | 11 |
Lisa | 16 |
Toby | 34 |
Note: You can’t use aliases in HAVING clauses, that’s why I typed SUM(Wins) out.
This query would exclude everyone with less than 10 wins in total. Pretty cool, huh?