Common SQL Mistakes

Common SQL Syntax Errors covers the mistakes a database refuses to run at all. This is the other, more dangerous category: queries that are perfectly valid SQL, run without complaint, and quietly return the wrong answer. Every example below was verified against a real database rather than reasoned about in the abstract — the wrong answers are real output, not a guess at what might happen.

Comparing to NULL with =

SELECT * FROM users WHERE active = NULL;  -- always returns zero rows

Verified: this runs without error and returns nothing, no matter what's actually in the active column — including rows where active genuinely is NULL. In SQL, NULL means "unknown," and comparing anything to an unknown value with = produces an unknown result, which WHERE treats as false — even NULL = NULL evaluates to NULL, not true. The only correct way to test for NULL is IS NULL / IS NOT NULL, which are special-cased in the grammar specifically because = can never do this job.

A LEFT JOIN that a WHERE clause quietly turns into an INNER JOIN

SELECT u.name, o.status
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed';  -- drops every user with zero orders, LEFT JOIN or not

The most common real LEFT JOIN bug in production code, and it produces no error — a user with no orders gets o.status = NULL from the join, and NULL = 'completed' is never true, so WHERE throws that row away exactly as if the join had never kept it. See LEFT JOIN vs INNER JOIN for the full trace-through and the fix (the condition belongs in the join's ON clause, not in WHERE, if unmatched rows are meant to survive).

SELECT * in application code

Fine for an ad-hoc query at a terminal, a real liability in code that runs repeatedly: every column gets fetched whether the caller uses it or not, and — more dangerously — code that accesses result columns positionally instead of by name silently breaks the moment someone adds a column in the middle of the table, or reorders one, with no error at the point the schema changed. Naming the columns you actually need is both faster and immune to this entire class of surprise.

A long OR chain instead of IN

WHERE status = 'pending' OR status = 'paid' OR status = 'shipped'
-- vs.
WHERE status IN ('pending', 'paid', 'shipped')

Both are logically identical and most modern optimizers treat them the same way — but IN reads at a glance as "one of these values" where an OR chain makes you parse three separate comparisons to arrive at the same conclusion, and that gap widens fast as the list grows. It also generalizes better: an IN list built from a subquery or an array parameter has no equivalent readable form as a chain of ORs.

UNION where UNION ALL was what you meant

SELECT status FROM orders UNION SELECT status FROM orders;
-- completed
-- pending

SELECT status FROM orders UNION ALL SELECT status FROM orders;
-- completed
-- pending
-- completed
-- completed
-- pending
-- completed

Verified against the same table unioned with itself: plain UNION deduplicates the combined result, UNION ALL keeps every row from both sides including duplicates. Reaching for UNION out of habit when the two sides can't actually produce duplicate rows (or when duplicates are exactly what you wanted to count) does real, silent damage — and even when the dedup is harmless, it's not free: UNION has to sort or hash the entire combined result to find the duplicates, work UNION ALL skips entirely. Default to UNION ALL unless you specifically need deduplication.

DISTINCT applied to the wrong thing

DISTINCT applies to the entire row the SELECT list produces, not to whichever single column you have in mind. SELECT DISTINCT customer_id, status dedupes on the combination of both columns — a customer with two different statuses across their orders still produces two rows, one per distinct combination, which surprises anyone expecting one row per customer.

Date-range filtering that silently drops the last day

WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31'

This looks like "all of January" and isn't, whenever created_at is a timestamp rather than a bare date. Verified against real timestamped rows: an event at 2026-01-31 09:00:00 and another at 2026-01-31 23:59:00 both got silently excluded, because the literal '2026-01-31' is interpreted as midnight at the very start of that day — BETWEEN is genuinely inclusive of both endpoints, but the upper endpoint here is "January 31st, 00:00:00," not "the end of January 31st." Only a row timestamped at exactly midnight would ever match on the last day. The fix is a half-open range that doesn't depend on knowing the last valid instant of a day at all:

WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'

Verified: this correctly includes both late-day January 31st timestamps that the BETWEEN version silently dropped. The same half-open pattern — >= start AND < oneUnitPastEnd — avoids this trap for any time-bucketed range, not just months.

N+1 queries from application code

Not a single wrong query, but a wrong shape: fetching a list of users, then looping over them in application code and running a separate SELECT * FROM orders WHERE user_id = ? for each one. It produces correct results and terrible performance — 1 query becomes 1 + N, scaling directly with the size of the list, when a single JOIN (or a single WHERE user_id IN (...)) would return everything needed in one round trip. Most ORMs have this failure mode baked in as the naive default and an explicit "eager load" or "include" option to fix it — worth checking whenever a page that lists N things feels slower than it should.

No index on a foreign key used in a JOIN

A missing index on the column a JOIN matches on doesn't make the query wrong, just slow in a way that gets worse as the table grows — every join has to fall back to scanning the whole table for a match instead of looking one up directly. Primary keys get an index automatically in virtually every database; the foreign key column pointing at that primary key generally does not, and has to be added explicitly. SQL Index Advisor reads a query's JOIN ON conditions and flags exactly this.

Try it yourself

SQL Query Validator catches the syntax-level mistakes this post deliberately doesn't cover — see Common SQL Syntax Errors for those. SQL Index Advisor and SQL Explain Helper both help catch the performance side of these mistakes before and after a query runs, respectively. All three run entirely in your browser.