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.
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.
Quick SQL gotcha fixes
Check exact string values, case, date ranges, NULL logic, and each WHERE condition one at a time.
Every plain SELECT column must be grouped; every summary value needs an aggregate.
Use a half-open range such as January 1 through before February 1.
A WHERE filter on the right table removes unmatched NULL rows.
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.
Usually a filter that does not match the real data: case, ranges, or AND/OR.
Integer division truncates. Multiply by 100.0 and round.
The default RANGE frame can group tied ORDER BY values. Use ROWS for row-by-row totals.
A single NULL in the list makes every NOT IN test unknown. Use NOT EXISTS.
With no ORDER BY, the database can return any rows in any order. Sort first.
Comparing a timestamp to a bare date cuts off that day. Use a half-open range.
A WHERE filter on the right table removes NULL matches. Move it into ON.
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 runs before OR. Add parentheses so the filter matches your intent.
UNION ALL stacks results exactly as written. Use UNION only when duplicates should collapse.
Want to practice spotting these? Work through Chapter 9: Query Debugging, then keep the SQL error message decoder nearby.