SQL Patterns and Gotchas: The Bugs Formatting Can't Fix (But Makes Visible)

Most SQL bugs aren't syntax — they're semantics that parse perfectly. Formatting doesn't fix them, but formatted SQL is how you see them. These are the recurring ones, each with the tell that makes it findable.

1. The LEFT JOIN that isn't

-- looks outer, behaves inner
FROM orders o
LEFT JOIN refunds r ON r.order_id = o.id
WHERE r.reason = 'damaged';

The WHERE filter rejects NULL, and unmatched rows are NULL on the right side — so every row the LEFT JOIN just preserved gets deleted downstream. The query silently computes "orders with refunds." Fix: move the condition to ON, or WHERE (r.reason = 'damaged' OR r.reason IS NULL). Indented join formatting (see best practices) puts ON and WHERE far enough apart to catch this in review.

2. NOT IN and the NULL that poisons everything

SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist);

If one user_id is NULL, three-valued logic makes every comparison UNKNOWN, and the result is an empty set — not "all users," not "all but the NULL," nothing. No error, just a shrug. Fix: NOT EXISTS (SELECT 1 FROM blacklist b WHERE b.user_id = users.id), which is NULL-safe and usually faster. Same trap in IN (subquery) yields only matching rows so the NULL hides; NOT IN is where it detonates.

3. Integer division truncates quietly

SELECT total_qty / order_qty FROM stats returns 0 for 7/8. Postgres truncates, MySQL yields decimals (silently different), SQLite truncates unless you * 1.0. A percentage report that's "always 0%" on Postgres and "sometimes correct" on MySQL is this, not a caching bug. Write 100.0 * a / b explicitly — the constant is documentation.

4. Timestamps without time zones

TIMESTAMP (Postgres) stores what you write with zero knowledge of what it means; TIMESTAMPTZ normalizes to UTC and renders per session. Mixing them in one schema gives you reports that drift by the user's DST, and the classic symptom is "dashboard numbers move when someone opens them from London in November." The migration is scary once; the alternative is forensic archaeology per incident.

5. Window functions and the tie problem

SELECT *, ROW_NUMBER() OVER (ORDER BY score DESC) AS rank FROM players;

Two players tied at 100: ROW_NUMBER picks a winner by physical order — nondeterministic between runs, pages, or plans. If ties are meaningful, use RANK/DENSE_RANK; if you're paging with WHERE rank BETWEEN x AND y, you need a tiebreaker in the ORDER BY (ORDER BY score DESC, id) or page boundaries will duplicate and drop rows. The formatted version makes the missing tiebreaker a two-token diff.

6. LIKE is doing regex's job badly

LIKE '%a_c%' matches axc — _ is a wildcard, and user-typed input through a LIKE is how you learn that your product name is now a regex engine. Escape %_ and backslash (or use ILIKE's escape, or parameterized full-text search). And LIKE with leading % never uses a plain B-tree index — that ORDER BY + LIMIT "optimization" you added just became a full scan; verify with EXPLAIN (ANALYZE, BUFFERS).

7. COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)

All three are correct and all three answer different questions: rows, non-null values, unique values. The audit-mismatch ticket "dashboard says 1,204, export says 1,203" is COUNT(col) over a nullable column, every time. Pick the question, write the count, name the alias after the answer (orders_with_refund not cnt).

8. Semicolons and statement stacking

Multi-statement strings (UPDATE ...; DROP TABLE ...) work in psql, fail in most drivers, and should fail in everything else. If a formatted query ever reveals a second statement hiding on the final line, that's a template-string bug and an injection surface in the same commit. The formatter surfaces it because one-statement-per-string is the invariant; the minifier keeps embedded copies honest.

FAQ

Why does my NOT IN query return zero rows when the subquery has one row? That one row is NULL. See trap #2 — rewrite as NOT EXISTS and it will make sense.

Does EXPLAIN actually execute my query? EXPLAIN no; EXPLAIN ANALYZE yes — including the INSERTs and DELETEs. Use EXPLAIN (ANALYZE, BUFFERS) inside a transaction you roll back on writes.

Formatting before every review — is that overkill? If the diff is formatted, no. Reviewers approve structure they can see; half the traps on this page were invisible in a single line, which is the whole argument of the best-practices guide.