10 Common SQL Mistakes Beginners Make (And How to Fix Each One)

A SQL mistake, at the beginner level, is rarely a random typo. It’s almost always the visible symptom of a wrong assumption about how a query actually gets executed — an assumption that felt reasonable, produced a query that ran without error, and then quietly returned the wrong answer. That’s what makes these mistakes dangerous: the query doesn’t crash. It just lies to you, confidently, and the output looks plausible enough that nobody double-checks it.

This post works through ten of the most common ones using a myth-versus-reality format. Each myth is the assumption that leads someone into the mistake. Each reality is what’s actually happening under the hood. The fix follows from understanding that gap, not from memorizing a rule.

Mistake 1: Assuming SELECT * Is Harmless

The myth: Pulling every column with SELECT * is just a convenient shortcut, and you can always narrow it down later if performance becomes an issue.

The reality: SELECT * pulls every column regardless of whether your query needs them, which means more data moving across the network, more memory consumed on both ends, and a query that silently breaks if someone renames or reorders a column your downstream code depended on by position. It also hides your actual data dependencies from anyone reading the query later — including future you.

The fix: Name the columns you need, every time, even when the table only has four of them. It costs a few extra keystrokes and saves you from a class of bugs that only show up after the schema changes.

Mistake 2: Confusing WHERE and HAVING

The myth: WHERE and HAVING both filter rows, so they’re interchangeable — just use whichever one comes to mind first.

The reality: WHERE filters individual rows before any grouping happens. HAVING filters groups after GROUP BY has already collapsed rows together, which means HAVING can reference aggregate results like SUM or COUNT, and WHERE cannot. Try to filter on an aggregate inside a WHERE clause and the database will reject the query outright, because that aggregate doesn’t exist yet at the point WHERE is applied.

The fix: Filter raw rows with WHERE. Filter the results of an aggregation with HAVING. If your filter condition mentions SUM, COUNT, AVG, MIN, or MAX, it belongs in HAVING.

Mistake 3: Treating NULL Like a Regular Value

The myth: You can check for NULL the same way you check for any other value — just write column = NULL and move on.

The reality: NULL represents an unknown value, not an empty one, and SQL’s three-valued logic treats any comparison against NULL as unknown rather than true or false. column = NULL never evaluates to true, even when the column actually contains NULL, because you’re comparing an unknown value to itself and getting another unknown back. Rows you expect to match simply vanish from your result with no error raised anywhere.

The fix: Use IS NULL or IS NOT NULL instead of the equality operator. These are the only constructs designed to test for the presence or absence of NULL correctly.

Mistake 4: Using COUNT(column) When You Mean COUNT(*)

The myth: COUNT(*) and COUNT(column_name) return the same number, so it doesn’t matter which one you reach for.

The reality: COUNT(*) counts every row in the result set, full stop. COUNT(column_name) counts only the rows where that specific column is not NULL, silently excluding the rest. Swap one for the other without thinking about it, and you’ll get two different totals from the exact same table — with no indication that anything’s wrong beyond a number that doesn’t match your expectations.

The fix: Use COUNT(*) when you want a plain row count. Use COUNT(column_name) only when you deliberately want to know how many rows have a non-NULL value in that particular column.

Mistake 5: Forgetting That GROUP BY Requires Every Non-Aggregated Column

The myth: You can GROUP BY one column and freely SELECT any other columns you like alongside your aggregate functions.

The reality: Every column in your SELECT list that isn’t wrapped in an aggregate function must also appear in your GROUP BY clause, or the database has no defined way to decide which of the many possible values for that column should represent the group. Some databases enforce this strictly and throw an error. Others (older MySQL configurations, notably) will pick a value for you arbitrarily, and that value can be effectively meaningless.

The fix: Every non-aggregated column in your SELECT list needs a matching entry in GROUP BY. If a column isn’t part of the grouping logic and isn’t wrapped in an aggregate, it usually shouldn’t be in the SELECT list at all.

Mistake 6: Writing a LEFT JOIN and Then Undoing It With WHERE

The myth: LEFT JOIN and WHERE are independent clauses, so filtering on a column from the joined table in your WHERE clause won’t affect which rows from your main table survive.

The reality: A WHERE clause runs after the join has already happened, and it evaluates against the full joined row — including any NULL values that LEFT JOIN inserted for unmatched rows. Filter on a column from the right-hand table in WHERE, and every row where that column came back NULL fails the comparison, silently reverting your LEFT JOIN back into something that behaves like an INNER JOIN.

The fix: If you need to filter the joined table while still preserving every row from the left table, move that condition into the ON clause instead of WHERE. Test any LEFT JOIN paired with a WHERE filter carefully — this is one of the easiest ways to lose rows without a single error message telling you so.

Mistake 7: Assuming Column Order in SELECT Doesn’t Matter for INSERT

The myth: As long as you provide the right number of values, an INSERT statement will match them to the correct columns automatically.

The reality: Without an explicit column list, INSERT INTO table VALUES (...) matches values to columns purely by position, in whatever order the table’s schema happens to define them. Add a column to that table later, or reorder columns during a migration, and every INSERT written this way starts placing values into the wrong slots — with no error, since the data types often still line up well enough to insert without complaint.

The fix: Always specify the column list explicitly in an INSERT statement: INSERT INTO table (column_a, column_b) VALUES (...). This makes the statement immune to schema reordering and self-documenting for anyone reading it later.

Mistake 8: Using != or <> Against a Column That Can Contain NULL

The myth: WHERE status != 'cancelled' will return every row that isn’t cancelled, including any rows where the status hasn’t been set.

The reality: Because of the same three-valued logic behind Mistake 3, any comparison against a NULL value — including != — evaluates to unknown, not true. Rows where the status column is NULL get excluded from both status = 'cancelled' and status != 'cancelled', which surprises almost everyone the first time it happens, since intuitively a NULL row should qualify as “not cancelled.”

The fix: When a column can contain NULL, decide explicitly whether NULL should count as a match. Write WHERE status != 'cancelled' OR status IS NULL if you want NULL rows included, or leave the condition as-is if you specifically want them excluded — just make that choice on purpose rather than by accident.

Mistake 9: Nesting Subqueries When a JOIN Would Be Clearer and Faster

The myth: Subqueries and JOINs are just stylistic alternatives — pick whichever one you’re more comfortable writing.

The reality: A correlated subquery placed in the SELECT list often re-executes once per row of the outer query, which scales poorly as your table grows. A JOIN, by contrast, typically lets the database’s query planner build a single efficient execution plan for the whole operation. The two aren’t always interchangeable in outcome, either — a subquery returning more than one row can crash a query that expected a single value, a failure mode a JOIN doesn’t share.

The fix: Reach for a JOIN when you’re combining data from two related tables and want every matching row. Reserve subqueries for cases where you genuinely need a single aggregated value, a existence check, or a filtered list, and check what your query plan looks like if performance is a concern.

Mistake 10: Ordering Results by Column Position Instead of Name

The myth: ORDER BY 2 is a harmless shorthand for “sort by whatever the second column in my SELECT list happens to be.”

The reality: It works, right up until someone reorders the SELECT list — adding a column near the top, removing one, or rearranging for readability — at which point the query keeps running without error and silently starts sorting by a completely different column. Nothing in the syntax warns you that the meaning has shifted.

The fix: Reference columns by name in ORDER BY, not by position. It’s more verbose, but it stays correct regardless of what happens to the rest of the SELECT list later.

The Pattern Behind All Ten

Look closely at these ten mistakes and a shared shape emerges: in every case, the query runs. Nothing throws an error. The output looks like a table of results, and it’s easy to trust a table of results that isn’t obviously broken. The actual failure is semantic — the query answers a slightly different question than the one you intended to ask, and the gap between those two questions is exactly the myth each section above describes.

That’s also why these mistakes survive so long in real codebases. A crashing query gets fixed immediately, because someone notices. A query that returns a plausible-but-wrong number can sit in a dashboard or a report for months before anyone questions it.

Pick the one from this list that most resembles a query you wrote last week. Chances are it’s not a coincidence — it’s the mistake your current mental model of SQL is most prone to producing, and now you have the reality that replaces the myth behind it.