Order of precedence in SQL: WHERE vs HAVING

If you're just getting started with SQL, this post is for you. So, it's worth looking at the order of precedence of SQL operators.

One particular case is WHERE vs HAVING, especially if you bind the aggregated column to the same column alias as in the input table.

This can save you from some unexpected results 😁

In short:
- WHERE = filter before aggregation
- HAVING = filter after aggregation

In the example below, the 'quantity' filtered in the HAVING clause is no longer the same 'quantity' in the original table, rather the SUM of quantities per each country bucket.

In practice, I'd rename the aggregated column to something like total_quantity to make it more readable.

Depending what we need, we pick which approach we take, filtering out records before or after aggregation.

SELECT
  country,
  SUM(quantity) AS quantity

FROM input_data

GROUP BY country

HAVING quantity > 0

SELECT
  country,
  SUM(quantity) AS quantity

FROM input_data

WHERE quantity > 0

GROUP BY country

---

Enjoyed this? Here are some related articles you might find useful:

• 9 tips on writing cleaner SQL
• Easy with that SELECT DISTINCT!
• Why you should use parentheses with AND & OR in SQL
• ORDER BY expressions in SQL