SQL and MySQL course Β· Module 6: Aggregation and grouping

WHERE versus HAVING

2 min read Β·
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 books

The most important difference

WHEREHAVING
When it actsbefore groupingafter aggregation
What it acts onindividual rowsgroups
Aggregate functionsCANNOT use themCAN 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. 1. At what moment does the WHERE clause act?

  2. 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:

  1. QuestionWhen WHERE acts
  2. QuestionWHERE/HAVING rule of thumb
  3. Coding taskOnly available books per category
  4. Put in orderA filter for available books
  5. Put in orderA filter for books published after 2000
  6. QuestionCondition on an aggregate
  7. Click in orderAggregation with a filter and sorting

Useful articles