Foundations — SQL clauses — WHERE, GROUP BY, HAVING, ORDER BY, LIMIT

Before you can understand when WHERE runs versus HAVING, you must be able to picture the raw stuff SQL moves around. This page builds every piece from the ground up, in the order each one depends on the last.

1. A table — the thing everything starts from

The picture: look at the grid above. The top strip (violet) holds the column names — name, dept, salary. Each horizontal stripe below is one row — one employee. That's it. A table is just labelled rows, and from now on we call the whole thing $R$.

Why the topic needs it: every clause in the parent — WHERE, GROUP BY, all of them — is an operation that takes this grid $R$ and produces a new, smaller or reshaped grid. If you can't see the grid, you can't see what the clauses do to it.

2. A row and a column — the two directions

3. A predicate — the yes/no test

We can write it as a little machine that eats a row and spits out a verdict:

Why three verdicts, not two? Because a value might be missing (unknown). Comparing to something unknown gives the third answer, NULL — covered in NULL handling and three-valued logic. That is why WHERE x = NULL returns nothing: the verdict is NULL, and NULL is not TRUE, so the row is dropped.

Why the topic needs it: WHERE keeps exactly the rows whose predicate answered TRUE. HAVING does the same trick but on piles. The predicate is the shared "yes/no" heart of both.

4. Filtering — the set-builder picture

The parent writes WHERE using set-builder notation. Let's earn every symbol. Recall from §1 that $R$ is our whole table and from §2 that $r$ is a single row inside it.

Put together:

The picture: every row is tested one at a time. Rows that pass (magenta ✓) fall through into the smaller output grid; rows that fail (faded) are dropped. Notice the output has the same columns — filtering never changes the shape sideways, only the number of rows.

Why the topic needs it: this is literally what WHERE does, and it's why WHERE can't use COUNT(*) — the test runs on one lonely row, which has no idea how many friends share its department.

5. A group — many rows collapsed into one

To describe "which pile does a row belong to?" we use a key function:

The picture: on the left, individual rows coloured by department. The arrows sweep same-coloured rows into one pile each on the right. Each pile becomes one output row. The number floating over each pile is $|G_v|$ — its COUNT.

Why the topic needs it: GROUP BY manufactures the piles; HAVING then throws away whole piles; aggregates are the only column-values that survive the squash.

6. Aggregate — one number from a whole column of a pile

Why the topic needs it: aggregates are the only way a group's data survives into the output, and they're the only expressions HAVING is allowed to test.

7. HAVING — filtering whole piles

Now that piles ($G_v$) and aggregates exist, we can give HAVING the same precise treatment we gave WHERE. WHERE tested one row with a predicate $p$; HAVING tests one pile with a pile-predicate $q$.

Why the topic needs it: this is the missing formal twin of WHERE. Seeing the two formulas side by side is the sharpest way to remember: WHERE keeps rows, HAVING keeps piles.

8. Sorting and slicing — ORDER BY, LIMIT and OFFSET

Once the rows are sorted they have positions $1,2,3,\dots$ — we can now cut by position:

The picture: unsorted rows (left) get lined up tallest-first by ORDER BY (middle), then LIMIT 3 keeps the top slice (right). Take away the sorting step and "top 3" is meaningless — you'd grab 3 random rows. That's the classic silent bug the parent warns about. OFFSET simply slides the kept window further down the sorted line.

Why the topic needs it: these are the last stations of the pipeline. They never filter or group — they only arrange and trim the finished result.

The prerequisite map — how the pieces feed the topic

The diagram below is a dependency map: an arrow $X\to Y$ reads "you need $X$ before $Y$ makes sense." Here is how to walk it, top to bottom:

1. Start at Table — everything is downstream of the grid.
2. A table splits into Rows and Columns (the two directions from §2).
3. A row feeds a predicate (the yes/no test), which powers WHERE.
4. A row also feeds the key function $k(r)$, which powers GROUP BY (the piles).
5. A column plus a group together feed an aggregate (one number per pile).
6. WHERE (filtered rows) and the aggregate both feed HAVING — you cannot filter piles until piles and their summaries exist.
7. Finally HAVING's surviving piles feed ORDER BY (sort) and then LIMIT (trim).

Trace any arrow backwards and you get a study plan: to learn HAVING, first master aggregates and groups; to learn those, first master rows and columns.

Equipment checklist

Cover the right side and test yourself — you are ready for the parent topic only when each is instant.