WHERE versus HAVING

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:

1SELECT kategoria_id, COUNT(*)
2FROM ksiazki
3WHERE dostepna = 1          -- first discard unavailable books
4GROUP 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:

1SELECT kategoria_id, COUNT(*)
2FROM ksiazki
3GROUP BY kategoria_id
4HAVING COUNT(*) > 3;        -- discard groups smaller than 4 books

The 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:

1SELECT kategoria_id, AVG(cena)
2FROM ksiazki
3WHERE dostepna = 1
4GROUP BY kategoria_id
5HAVING AVG(cena) > 40
6ORDER BY AVG(cena) DESC;

This is the full song of aggregation: filter rows, group, filter groups, sort. A masterful command!