SQL and MySQL course Β· Module 6: Aggregation and grouping
WHERE versus HAVING
In this lesson4
This question confuses many scribes: when to use WHERE, and when HAVING? Both filter data, but at a completely different moment and on a different level. Let us solve this riddle once and for all.
WHERE filters rows - BEFORE grouping
WHERE acts on individual rows, before anything is grouped. It discards scrolls that do not meet the condition:
SELECT kategoria_id, COUNT(*)
FROM ksiazki
WHERE dostepna = 1 -- first discard unavailable books
GROUP BY kategoria_id;Here MySQL first throws out all unavailable books, and only then groups and counts those that remain.
HAVING filters groups - AFTER aggregation
HAVING acts on finished groups, after the aggregates are computed. It discards whole groups that do not meet the condition:
SELECT kategoria_id, COUNT(*)
FROM ksiazki
GROUP BY kategoria_id
HAVING COUNT(*) > 3; -- discard groups smaller than 4 booksThe most important difference
| WHERE | HAVING | |
|---|---|---|
| When it acts | before grouping | after aggregation |
| What it acts on | individual rows | groups |
| Aggregate functions | CANNOT use them | CAN use them |
Rule of thumb: if the condition concerns an ordinary column (dostepna = 1, rok_wydania > 2000) - use WHERE. If the condition concerns the result of an aggregation (COUNT(*) > 3, AVG(cena) > 50) - use HAVING.
Both at once
We often use them together - WHERE narrows the data, HAVING selects the groups:
SELECT kategoria_id, AVG(cena)
FROM ksiazki
WHERE dostepna = 1
GROUP BY kategoria_id
HAVING AVG(cena) > 40
ORDER BY AVG(cena) DESC;This is the full song of aggregation: filter rows, group, filter groups, sort. A masterful command!
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. At what moment does the WHERE clause act?
2. The condition concerns an ordinary column: dostepna = 1. Which clause should you use?
These are 2 of 3 questions for this lesson. Solve the rest in the game.
Hands-on tasks in the game
- Code editor
Write a query that selects kategoria_id and COUNT(*) from the ksiazki table, takes into account only the rows where dostepna = 1, and groups the result by kategoria_id.
- Horizontal ordering
Arrange the tokens on a single line to form a clause that filters available books:
- Horizontal ordering
Arrange the tokens on a single line to form a clause that filters out books published after the year 2000:
- Click in order
Click the clauses in the correct order in which they must appear in an aggregating query with a filter and sorting: