Six SQL Mistakes Beginners Make

The queries that run and still lie to you

Syntax errors are the easy failures. The database refuses the query, you read the message, you fix it. The dangerous mistakes are the ones that produce a tidy result set that happens to be wrong: a report that quietly drops customers, a count that is short by exactly the number of NULLs.

Every example below runs against the live datasets on this site. Run each one, then run the corrected version, and compare the row counts.

1. Comparing to NULL with the equals sign

NULL does not mean "empty". It means unknown, and any comparison with an unknown value is itself unknown, never true. So manager_id = NULL matches nothing at all, even when rows have a NULL manager.

Zero rows. The correct operator is IS NULL, which asks about the state of the value rather than comparing it.

Five employees have no manager. The first query did not error, it just silently agreed with nothing.

2. NOT IN with a subquery that can return NULL

This is the same rule wearing a disguise, and it is the single most expensive NULL bug in production SQL. "Which employees are not anybody's manager?" looks like a job for NOT IN.

Empty. The subquery returns a NULL among the manager IDs. id NOT IN (1, 5, NULL) expands to id <> 1 AND id <> 5 AND id <> NULL, and that last term is unknown, so the whole condition can never be true.

3. Filtering a LEFT JOIN in the WHERE clause

You write a LEFT JOIN precisely because you want every row from the left table, including the ones with no match. Then you add a condition on the right table in WHERE, and the join silently becomes an inner join: rows with no match have NULLs on the right, and NULL = 'completed' is not true.

Fewer than fifteen customers come back, even though the store has fifteen. Move the filter into the ON clause, where it restricts what counts as a match instead of throwing rows away after the join.

All fifteen customers, with zeroes where they belong. The rule of thumb: a condition on the left table belongs in WHERE, a condition on the outer joined table belongs in ON.

4. Using WHERE when you mean HAVING

WHERE runs before rows are grouped, HAVING runs after. You cannot filter on COUNT(*) in WHERE, because at that point the count does not exist yet. The two are not interchangeable, and using the wrong one either errors or, worse, filters the wrong stage.

Read it as two filters: WHERE decides which products enter the grouping, HAVING decides which groups survive it.

5. Trusting row order without ORDER BY

Results often come back in insertion order on a small table, so beginners conclude the order is guaranteed. It is not. The moment the planner picks a different scan, adds parallelism, or the table grows past a threshold, the order changes, and a LIMIT 5 that once returned the five newest rows starts returning five arbitrary ones. If order matters, say so explicitly, and make the sort key unique enough to be deterministic.

6. COUNT of a column is not COUNT of the rows

COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. On a nullable column those are different numbers, and the gap is easy to miss in a dashboard.

The same applies to AVG, SUM and MIN. Every aggregate except COUNT(*) skips NULLs rather than treating them as zero. An average salary computed over a column with missing values is an average of the rows that had values, which is usually not the number the business asked for.

Catching these before they ship

The habit that catches all six is comparing counts. Run the query, then run SELECT COUNT(*) on the base table, and explain the difference out loud. If you cannot account for the missing rows, one of these six is why.