SQL problems
SQL problems, worked with real output
One page per problem that costs people an afternoon. Two tiny tables you can hold in your
head, the query, the rows it returns, and why those rows and no others. Every statement and
every result table on these pages was executed on DuckDB and on SQLite before it was
published, by scripts/landing/verify-sql-pages.mjs in the game's repository.
Why did my JOIN duplicate rows?
One order, two shipments, and a revenue total that comes out 125 too high. The row pairing spelled out, then three fixes with the numbers each one returns.
JOIN · fan-out · aggregationCOUNT(*) vs COUNT(column) after a LEFT JOIN
The student who submitted nothing gets a 1 from COUNT(*) and a 0 from COUNT(submission_id). The padded row that causes it, and the second trap underneath.
LEFT JOIN · NULL · COUNTJOINs Field Notes
The long-form companion: keys and grain, the pairing promise, LEFT JOIN, ON against WHERE, anti-joins and fan-out, with an answer key. Free to read online, and as two PDF editions.
Guide · free PDF