Category Archives: SQL

Difference between WHERE and HAVING in T-SQL Statements

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?