SQL clauses — WHERE, GROUP BY, HAVING, ORDER BY, LIMIT: Visual walkthrough

We build everything from scratch. If you have never written a line of SQL, start at Step 1 line one.

The raw material

Here is our whole world — a tiny table called employees. Each row is one person. Each column is one fact about that person. That's all a table is: a grid of rows (records) and columns (fields). See SQL SELECT basics if the word SELECT is brand new.

Definition Our example table

name	dept	salary
Ana	Sales	60000
Ben	Sales	55000
Cy	Sales	0
Dee	Eng	90000
Eli	Eng	85000
Fay	HR	40000

Six rows. Three departments. One salary of 0 (a data-entry mistake we will want to drop).

And here is the ONE query we will trace, start to finish:

Formula The query under the microscope

SELECT   dept, COUNT(*) AS headcount
FROM     employees
WHERE    salary > 0
GROUP BY dept
HAVING   COUNT(*) >= 2
ORDER BY headcount DESC
LIMIT    2;

We wrote it in this order. The engine will run it in a different order. Watch.

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

The picture above is our map: seven numbered boxes, the order the engine actually executes. Every step below lights up one box.

Step 1 — FROM: gather the raw rows

WHAT. The engine ignores everything you typed except FROM employees and grabs the entire table — all six rows, untouched.

WHY. You cannot filter, group, or sort rows you have not yet fetched. FROM (and any JOINs) is the source of water for the whole pipeline. Nothing exists before it.

PICTURE. Below, all six rows sit in the pipe. Nothing is coloured out yet — this is the input.

$the whole table R = {r_{1}, r_{2}, r_{3}, r_{4}, r_{5}, r_{6}}$

$R$ — the set of every row in the table.
$r_{1} \dots r_{6}$ — the six individual people. Each $r_{i}$ carries its own name, dept, salary.

At this stage there are no groups, no aggregates, no aliases — just raw rows. Remember that; it explains the next three steps.

Step 2 — WHERE: filter one row at a time

WHAT. For each row, we test the condition salary > 0. If the test is TRUE, the row survives. If it is FALSE (or NULL), the row is dropped. Cy has salary = 0, so 0 > 0 is FALSE — Cy is removed.

WHY. salary > 0 is a per-row fact: you can answer it looking at a single person, with no need to know anyone else. That is exactly what WHERE is for. And because groups do not exist yet (Step 1 gave us bare rows), WHERE cannot mention COUNT(*) — there is nothing to count over.

PICTURE. Cy's row turns grey (dropped); five rows continue. Notice the test runs independently on each row — like a bouncer checking one ID at a time.

$WHERE (R, p) = {r \in R : p (r) = TRUE}, p (r) = (r . salary > 0)$

$p (r)$ — the predicate: a yes/no question asked of one row $r$ .
$r . salary$ — this particular row's salary value.
$= TRUE$ — the row is kept only if the answer is exactly TRUE. Not FALSE, and (crucially) not NULL.

After Step 2 the survivors are: Ana, Ben, Dee, Eli, Fay.

Step 3 — GROUP BY: collapse rows into piles

WHAT. Take the five survivors and sort them into piles, one pile per distinct dept value. Sales gets {Ana, Ben}. Eng gets {Dee, Eli}. HR gets {Fay}. Each pile will become one output row.

WHY. We asked for a per-department count. A count is meaningless for a single row — it only makes sense over a collection. GROUP BY is the machine that builds those collections. This is where aggregate functions finally have something to chew on.

PICTURE. Three coloured piles form. The number of rows that fell into each pile is written on it — that number is COUNT(*).

$groups = {G_{v} : G_{v} = {r : k (r) = v}}, k (r) = r . dept$

$k (r)$ — the key function: it reads the grouping column (dept) of a row.
$v$ — one distinct key value: Sales, Eng, or HR.
$G_{v}$ — the pile of all rows sharing key $v$ . $∣ G_{v} ∣$ (the pile's size) is what COUNT(*) returns.

Piles after Step 3: Sales (size 2), Eng (size 2), HR (size 1).

Step 4 — HAVING: filter whole piles

WHAT. Now test each pile against COUNT(*) >= 2. Sales (2) �myes. Eng (2) ✓ yes. HR (1) ✗ no — the entire HR pile is thrown away.

WHY. COUNT(*) >= 2 is a per-pile fact — you must know how big the pile is, which only exists after grouping. That is why HAVING lives here and not in WHERE. HAVING is "WHERE, but for groups".

PICTURE. The HR pile (size 1) greys out and drops. Sales and Eng remain. Compare with Step 2: same idea (drop what fails), different unit (piles, not rows).

$HAVING ({G_{v}}, q) = {G_{v} : q (G_{v}) = TRUE}, q (G_{v}) = (∣ G_{v} ∣ \geq 2)$

$q (G_{v})$ — a predicate on a whole pile, not a single row.
$∣ G_{v} ∣$ — the pile's size, i.e. COUNT(*). HR has $∣ G_{v} ∣ = 1 < 2$ , so it fails.

Survivors after Step 4: pile Sales (2), pile Eng (2).

Step 5 — SELECT: build the output columns and name them

WHAT. Now, at last, SELECT runs. It computes the output columns for each surviving pile: the grouping column dept, and the aggregate COUNT(*), which we label AS headcount. Two output rows are born: (Sales, 2) and (Eng, 2).

WHY. Aliases like headcount are invented here, in Step 5. This single fact explains the most famous SQL bug: WHERE (Step 2) and HAVING (Step 4) ran earlier, so they have never heard of headcount — the name did not exist yet. Only steps after SELECT can use it.

PICTURE. Each pile becomes a clean two-column row. The new column gets its sticky-note name headcount.

$row (G_{v}) = (dept v, headcount ∣ G_{v} ∣)$

$v$ — the pile's key, output as the dept column.
$∣ G_{v} ∣$ — the count, output and renamed headcount for the rest of the query.

Output so far (unsorted): (Sales, 2), (Eng, 2).

Step 6 — ORDER BY: sort the finished rows

WHAT. Sort the two output rows by headcount DESC (biggest first). Both have headcount = 2 — a tie. With no tie-breaker, the engine may return them in either order; we'll pick a stable display for the picture.

WHY. ORDER BY runs after SELECT (Step 5), so it is allowed to say headcount — the alias now exists. (Try WHERE headcount > 1 and it errors, because WHERE was Step 2, long before the name was born.) Sorting must come before LIMIT: "top N" is only defined once there is an order.

PICTURE. The two rows line up, tallest bar on the left. The DESC arrow points from big to small.

$ORDER BY headcount DESC \Rightarrow sort rows so headcount_{first} \geq headcount_{next}$

DESC — descending, high to low. (ASC, the default, is low to high.)
Ties (equal headcount) have no guaranteed order unless you add a tie-breaker like , dept ASC.

Step 7 — LIMIT: keep only the top slice

WHAT. LIMIT 2 keeps at most the first 2 rows of the sorted result. We have exactly 2, so both stay. Had there been more, the rest would be discarded.

WHY. LIMIT is the last thing that runs — it trims an already sorted list. "Top 2" is only meaningful because Step 6 already ordered the rows. LIMIT n OFFSET m skips $m$ then takes $n$ ; that is the engine of pagination (and Indexes make it fast).

PICTURE. A window frame drops over the sorted list, showing the top 2 and hiding everything below the cut line.

$LIMIT n = keep the first n rows of the sorted result (n = 2)$

$n$ — how many rows to hand back. Rows beyond position $n$ are silently dropped.
With OFFSET m: skip $m$ first, then take $n$ — page $k$ is OFFSET (k-1)*n.

Final answer: (Sales, 2), (Eng, 2).

The one-picture summary

Here is the entire journey on one canvas: six raw rows enter on the left, and at each stage the pipe narrows — WHERE drops a row, GROUP BY builds piles, HAVING drops a pile, SELECT labels, ORDER BY sorts, LIMIT trims — until the final two rows exit on the right.

Recall Feynman retelling — the whole walkthrough in plain words

Picture a big box of trading cards, one card per employee.

FROM — tip the box out: all six cards on the table.
WHERE — flick away any card you personally dislike, one at a time. Cy has a salary of 0? Gone. (You judge each card alone — you can't yet say "keep departments with 5+ people", because there are no departments-as-piles yet.)
GROUP BY — sweep the survivors into piles, one pile per team. Now piles exist, and each pile has a size.
HAVING — throw away whole piles that are too small. HR has only one card? The whole pile goes. (You couldn't do this in step 2 — the pile didn't exist yet. That single fact is why HAVING is separate from WHERE.)
SELECT — write a neat summary card for each remaining pile: team name + how many. Give the count a nickname, "headcount". The nickname is born here — that's why steps 2 and 4 never heard of it.
ORDER BY — line the summary cards up, biggest headcount first.
LIMIT — hand over just the top two; sweep the rest off the table. You can't trim the "top 2" before you've lined them up, and you can't line up piles before the piles exist. The whole confusing order is really just common sense: you can't act on a thing before you've made it.

Recall Quick self-test

At which step is the alias headcount first usable? ::: Step 5 (SELECT) onward — so ORDER BY (Step 6) can use it, but WHERE (Step 2) and HAVING (Step 4) cannot. Why does HR disappear and where? ::: At Step 4 (HAVING), because its pile size is 1, failing COUNT(*) >= 2. If we removed ORDER BY, would LIMIT 2 still be correct? ::: No — without a sort, LIMIT returns 2 arbitrary rows, not the top 2.