Querying

Lookup sheet for stage 2. The question it exists to answer: which join, subquery or set operation answers this question, and where does a condition belong?

All counts below are against the small fixture: eight customers, twelve orders, eight countries, described in The Dataset.

Every join type

A RIGHT JOIN is never a distinct operation: swapping which table is named first and writing LEFT JOIN instead gives the identical rows, and reads in the direction everyone already scans FROM in.

ON versus WHERE

For an inner join the two are interchangeable: customers, orders WHERE customers.id = orders.customer_id and customers JOIN orders ON c.id = o.customer_id both return 12. The equivalence breaks once a join can invent a row.

Verified on customers LEFT JOIN orders ON o.customer_id = c.id: adding WHERE o.amount > 100 gives 5 rows; moving the identical condition into ON ... AND o.amount > 100 gives 8, one per customer. The one safe test to run in WHERE after an outer join is IS NULL on the invented side, since IS NULL never returns unknown: that idiom is the anti-join, WHERE o.id IS NULL after customers LEFT JOIN orders, isolating customer 6 alone.

What each aggregate does with NULL

Every aggregate except count(*) ignores NULL: it is treated as absent from the calculation, not as zero.

Verified on orders (12 rows, shipped_at missing on four): count(*) 12, count(shipped_at) 8, sum(amount) 2406.49, avg(amount) 200.5408333333333333. Verified with WHERE amount > 100000 (zero rows survive): count(*) 0, sum(amount) and avg(amount) both NULL. coalesce(sum(amount), 0) turns that blank into a 0 a caller can print without it reading as a failed query.

GROUP BY treats NULL differently from WHERE and a join condition: WHERE country = 'GB' and a join's ON both refuse to match a NULL, but GROUP BY puts every NULL into one group rather than dropping the rows, since grouping only needs somewhere to put a row it cannot compare. Verified: customers JOIN orders GROUP BY c.country gives five groups, not four, the fifth holding both NULL-country customers with 2 orders and 700.00.

Where a condition belongs

Tied to Evaluation order: ON and FROM run first, WHERE second, GROUP BY third, HAVING fourth, then SELECT.

HAVING needs no GROUP BY: with none, the whole table is one group, and HAVING can still reject it. Verified: SELECT count(*) FROM orders HAVING count(*) > 5 returns 12; the same query with > 500 returns no rows at all, not a row holding 0, the one way an aggregate query with no rows can come back empty.

The diagnostics of the stage

Every error a lesson quoted, re-run and confirmed.

All four 42803 errors share one SQLSTATE: a clause was given something it cannot have yet at the point it runs.

Which subquery form answers which question

The NOT IN and NULL rule. NOT IN expands to a chain of <> joined by AND; one NULL anywhere in the subquery's result makes that chain unknown for every row, not just the row connected to the NULL, so WHERE keeps nothing. Verified: id NOT IN (SELECT customer_id FROM orders) returns customer 6, correctly, since customer_id is NOT NULL. Add one NULL to the same set, ... UNION SELECT NULL, and the identical query returns zero rows. NOT EXISTS asks a different question, whether a matching row exists, and never expands into a comparison against every value, so it returns customer 6 either way. Default to NOT EXISTS; use NOT IN only against a column verified NOT NULL, never one merely assumed to be.

The four set operations

Over orders, one branch for amount > 100, one for shipped_at IS NULL:

An ALL variant exists for every operator except plain UNION, which already has UNION ALL as its counterpart, and keeps a row exactly as many times as it was produced: amount FROM orders WHERE customer_id = 4 EXCEPT amount FROM orders WHERE customer_id = 999 returns 2 rows, since the plain form deduplicates a genuine repeated 10.00; the EXCEPT ALL form of the identical query returns 3.

Columns match by position, not by name: the output header comes entirely from the first branch. ORDER BY cannot sit inside an unparenthesised branch, only after the last branch, where it orders the combined result, or inside a parenthesised branch that carries its own. Without a trailing ORDER BY, a set operation's result has no defined order at all: the plain UNION above, run with nothing after it, came back as 103, 104, 105, 106, 110, 101, 108, 112, matching neither branch's own order.

EXCEPT is a third way to write an anti-join, alongside LEFT JOIN ... IS NULL and NOT EXISTS: customers EXCEPT SELECT customer_id FROM orders returns customer 6 alone, the identical answer. It differs from the other two because it compares whole rows and deduplicates by nature, so it cannot return a column the second query lacks, where a filter never has that restriction.

PostgreSQL and SQLite

Most of this stage runs identically on both engines: every join type, including FULL JOIN and RIGHT JOIN, gives the same counts; FILTER works the same way; the NOT IN-and-NULL trap behaves identically; grouping by a SELECT alias is accepted by both.

GROUPING SETS and ROLLUP are the one piece of this stage that is PostgreSQL only.