Common Mistakes to Avoid

Most SQL bugs aren't exotic — they're the same handful of gotchas showing up again and again, often because the mistake looks perfectly reasonable at first glance. This page is a quick reference to the ones that trip people up most often, so you can recognize them before they cause a problem instead of after.

Mistake	Why it hurts
Forgetting `WHERE` on `UPDATE`/`DELETE`	Without a `WHERE` clause, the statement applies to every row in the table — a one-word omission can wipe out or overwrite an entire table.
Using `= NULL` instead of `IS NULL`	`NULL` represents "unknown," and comparing anything to it with `=` also returns unknown (treated as false) rather than true — `column = NULL` never matches any row, even NULL ones. Use `IS NULL` / `IS NOT NULL`.
Confusing `WHERE` and `HAVING`	`WHERE` filters individual rows before grouping happens; `HAVING` filters groups after aggregation. Putting an aggregate condition like `COUNT(*) > 5` in `WHERE` fails outright, and putting a plain row filter in `HAVING` works but is needlessly slower.
`NOT IN` with a list that contains `NULL`	If the subquery or list used with `NOT IN` produces even one `NULL`, the entire condition evaluates to unknown for every row, and the query returns no rows at all — a silent, confusing result. Prefer `NOT EXISTS` when `NULL`s are possible.
`COUNT(*)` vs `COUNT(column)`	`COUNT(*)` counts every row regardless of `NULL`s; `COUNT(column)` only counts rows where that specific column is not `NULL`. Using them interchangeably gives silently different, and sometimes wrong, totals.
Assuming `SELECT *` returns columns in a stable order	Column order is not a guaranteed contract — a later schema change can reorder or add columns, silently breaking code that reads results positionally instead of by name.
Building queries with string concatenation	Splicing user input directly into a SQL string opens the door to SQL injection and breaks on ordinary input like an apostrophe in a name. Always use parameterized queries.
Over-indexing or under-indexing	Too few indexes means slow lookups and full table scans on large tables; too many indexes slows down every `INSERT`/`UPDATE`/`DELETE`, since each index has to be maintained on every write.
Skipping transactions on multi-step operations	Running related statements independently means a failure partway through can leave data in an inconsistent state, with some changes applied and others not.
Ignoring `EXPLAIN` output	Assuming a query is fast because it "looks simple" instead of checking whether the planner is actually using an index — a missing index often only becomes visible in `EXPLAIN`, not in a quick manual test on a small table.

The NULL comparison trap, illustrated

This returns zero rows, even for rows where email really is NULL

SQL

SELECT * FROM users WHERE email = NULL;   -- always empty, this is wrong

SELECT * FROM users WHERE email IS NULL;  -- correct

The NOT IN with NULLs trap, illustrated

A single NULL in the subquery silences the whole query

SQL

-- if any refunded order has a NULL customer_id, this returns NO rows,
-- even for customers who clearly never appear in refunded_orders:
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM refunded_orders);

-- NOT EXISTS does not have this problem:
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM refunded_orders r WHERE r.customer_id = c.id
);

Most of these mistakes are silent

Notice that almost none of these produce an error message — they return a result that looks plausible but is subtly wrong, or in the case of a missing `WHERE`, a result that is dramatically wrong but only discovered after the damage is done. Testing on realistic data, using `SELECT` to verify before mutating, and checking `EXPLAIN` output are what catch these before they reach production.