Field manual · SQL traps

SQL traps: queries that run and are wrong

Fifteen SQL mistakes that don't throw an error: each with a minimal dataset, the broken query, the fixed one and why. Checked in DuckDB.

The join that multiplies rowsAggregating after a one-to-many JOIN duplicates every row on the "one" side once per matching row on the other side, and every sum computed afterward comes out inflated.
A rate needs a denominatorA raw count alone rewards whoever shows up most; a rate with no minimum sample rewards whoever shows up least. You need both at once.
A WHERE on the right-hand table turns your LEFT JOIN back into an INNER JOINON says how rows are glued together; WHERE decides which survive. With INNER JOIN the order doesn't matter; with LEFT JOIN, filtering in the WHERE on a column from the added table strips out exactly the unmatched rows the LEFT JOIN was there to keep.
Survivorship bias lives in the JOINJoining a history table against today's catalog measures who survived, not what actually happened: the ones that disappeared are exactly the ones carrying the bad news.
Time zones: store UTC, show localGrouping by day without deciding the time zone splits the same session across two days, and a clock change turns one day into 23 hours and the next into 25.
Why does NOT IN return zero rows?NOT IN against a list with a single NULL throws out the whole query without a warning. It is the worst possible failure: it doesn't crash, it gives false confidence.
Why does COUNT(column) give a smaller number than COUNT(*)?COUNT(*) counts rows. COUNT(column) counts that column's non-null values. If the column allows NULL, the two numbers almost never match, and the gap isn't a bug: it's the nulls.
Why doesn't LIKE find a name that's right there in the table?LIKE compares character by character and is case-sensitive. If the real data comes in uppercase, lowercase or without accents, a well-written pattern misses rows sitting right in front of it.
Why does the biggest transfer of the month show up as $990?If the amount was stored as text, ORDER BY compares it character by character: '9' comes before '75000' because the first character of '990' is bigger, no matter what digits follow.
Why does BETWEEN eat the last day of the range?BETWEEN 'a' AND 'b' on a TIMESTAMP turns 'b' into 'b 00:00:00'. Everything that happened after midnight on that day is left out, even though the whole day was supposed to count.
Why does a self-join always bring back one extra row?Joining a table against itself without excluding the row-against-itself match means every record pairs up with its own copy: a row that says nothing, but still counts.
When do two time intervals actually overlap?Comparing only the start or only the end isn't enough, and using <= instead of < turns two shifts that merely touch at one point into an overlap that isn't real.
Why does the running balance repeat the exact same number on two different rows?sum(...) OVER (ORDER BY date) with no explicit frame defaults to RANGE: it groups every row with the same date into one block, and all of them show the same running total.
Why can't WHERE filter on a window function's result?WHERE runs before the columns computed with OVER even exist. Filtering on a window function needs QUALIFY, or wrapping the query in a CTE and filtering from the outside.
Why doesn't WHERE find the customer who 'always' pays cash?Filtering in the WHERE before grouping answers a different question: it counts who paid cash several times, it doesn't check they ALWAYS did. 'Always' describes the whole group, and that belongs in HAVING.

Would you spot them with no warning? One a day, four options.

Daily trap →