AI Hints
Homeโ€บSQL Tutorialโ€บFiltering Groups
SQL ยท Lesson 40 of 126

WHERE versus HAVING

Before and after grouping.

โฑ 2 min readโ–ถ Video at 55:48๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Where and having both filter. So how do you choose? It's all about timing. One runs before the piles are made, and the other runs after.

WHERE versus HAVING
From the video: WHERE versus HAVING
  • Both of them filter
  • It's about timing
  • Before, or after grouping

Before and after

Where runs before grouping. It looks at single rows, like a book's price. Having runs after grouping. It looks at whole groups, like the number of books in a genre. If your test uses count, sum, average, min, or max, it belongs in having. Otherwise, use where.

WHERE

  • Before grouping
  • Tests single rows
  • price > 15

HAVING

  • After grouping
  • Tests whole groups
  • COUNT(*) >= 3

Both in one query

Here's a query with both. First, where keeps only books over fifteen dollars. Then group by makes a pile per genre. Then having keeps genres with at least three of those pricey books. Run it. Five genres have three books over fifteen dollars.

query.sqlsql
SELECT genre, COUNT(*) AS pricey_books
FROM books
WHERE price > 15
GROUP BY genre
HAVING COUNT(*) >= 3;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   genre    โ”‚ pricey_books โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Business   โ”‚ 3            โ”‚
โ”‚ Cooking    โ”‚ 3            โ”‚
โ”‚ Fantasy    โ”‚ 3            โ”‚
โ”‚ History    โ”‚ 3            โ”‚
โ”‚ Technology โ”‚ 3            โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

The order SQL really works in

Here's a secret. SQL doesn't run your query in the order you write it. It starts with from, then where, then group by, then having, then select, then order by, and finally limit. That's why you can't use a column alias in where. When where runs, select hasn't happened yet.

  1. FROMget rows
  2. WHEREfilter rows
  3. GROUP BYmake piles
  4. HAVINGfilter piles
  5. SELECTpick columns
  6. ORDER BYsort
The order SQL really works in
From the video: The order SQL really works in

Summary

Where filters rows before grouping, having filters groups after. And SQL runs from, where, group by, having, select, order by. Next, a powerful trick that combines case with sum.

Key takeaways
  • WHERE: rows, before
  • HAVING: groups, after
  • FROM first, SELECT later
Next up: Counting with Conditions โ€” CASE inside SUM