Common SQL gotchas
The SQL mistakes that catch almost everyone: queries that return no rows, GROUP BY errors, missing dates, inflated counts, NULL surprises, and JOIN filters that silently drop data.
SQL mistakes are often quiet: the query runs, but the answer is empty, inflated, truncated, or missing rows you expected to keep. Sometimes the database rejects the query with a clause-order error; other times it accepts the query but the result still needs investigation.
Use these pages as a debugging checklist. If a filter returns no rows, check NULL handling and AND/OR logic. If a count looks too high, inspect the join shape and whether COUNT(column) skipped NULL values. If a metric was averaged twice, recompute it from totals. If a LEFT JOIN loses rows, look for right-table filters in WHERE. If GROUP BY fails, compare each selected column with the grouped or aggregated columns, as the GROUP BY error guide explains.
The fixes are intentionally small and testable: isolate the table, inspect a few rows, add one condition at a time, then compare the row count before and after each change. That habit catches more SQL bugs than memorizing error text.
Every gotcha, one page each
Comparisons to NULL return unknown, so the row is dropped. Use IS NULL.
A one-to-many join repeats rows, inflating totals. Count what you mean.
A missing join condition pairs every row with every row. Add the key match.
When both sides have repeats, the join multiplies combinations. Pre-aggregate first.
Names and labels can repeat or change. Join on stable ids whenever the schema gives you one.
Often a filter or join does not match the real data: check case, ranges, NULLs, and boolean logic.
Some SQL engines truncate integer division. Use a decimal operand when you need a fractional result.
The default RANGE frame can group tied ORDER BY values. Use ROWS for row-by-row totals.
A single NULL in the list means no NOT IN test can be true. Use NOT EXISTS.
Without ORDER BY, SQL does not guarantee which rows come first. Sort when the selected rows or their order matter.
Comparing a timestamp to a bare date cuts off that day. Use a half-open range.
A null-rejecting WHERE filter on the right table removes unmatched LEFT JOIN rows.
A NULL key matches nothing, so the row leaves the join. Use a LEFT JOIN.
Relationship predicates belong in ON; row filters belong in WHERE. Mixing them hides bugs.
COUNT(*) counts rows, but COUNT(column) counts only non-NULL values.
An average of group averages ignores group size. Recompute from totals.
AND binds more tightly than OR. Add parentheses so the filter matches your intent.
UNION ALL stacks results exactly as written. Use UNION only when duplicates should collapse.
Text sorts character by character, so '9' lands above '75'. Cast to a number before sorting or comparing.
A DOUBLE column stores cents approximately, so a SUM drifts off by a hair. Cast to DECIMAL before you add.
Tied rows share rank 1, so a top-1-per-group query can return two winners. Break the tie on purpose.
Ids record when a row was inserted, not when the thing happened. Order by the date to find the latest.
Dividing by a zero prior period gives Infinity or NaN in DuckDB. Guard with NULLIF and label the new rows.
Each share rounds on its own, so the column can total 99 or 101. Give the missing points to the largest remainders.
ROW_NUMBER() with no ORDER BY keeps whichever row the engine reads first. Say which row should win.
A session that views a page twice counts twice. Count distinct sessions or users at every stage.
The default window frame stops at the current row, so LAST_VALUE sees nothing after it. Extend the frame, or use FIRST_VALUE.
Stray spaces and capitals make 'Perth' and ' PERTH ' different values. Group by a cleaned key, or clean the column.
In LIKE, _ matches any one character and % any run. Escape them to match the characters themselves.
Replacing NULL with 0 before avg counts missing values as real zeros. Let avg skip them, and count them separately.
DuckDB and PostgreSQL replace the first match unless you pass 'g'. MySQL, Oracle, and Snowflake replace them all.
DISTINCT ON keeps the first row of each group in ORDER BY order. Sort by the key alone and the row kept is whichever comes first.
UNION removes duplicate rows, so equal amounts from different records collapse into one. Use UNION ALL to stack results.
An alias that reuses the column name makes ORDER BY sort the rounded value, and its ties change who makes the cut.
Text compares character by character, so '11/03/2024' comes after '11/01/2025'. Parse it into a date before comparing.
A key padded with trailing spaces never equals the clean one, so an inner join silently drops the rows. Join on trim(), or clean the column.
Want to practice spotting these? Work through Chapter 9: Query Debugging, then keep the SQL error message decoder nearby.