Common SQL Mistakes and How to Fix Them (Including the Ones by Engine)

The Quick Answer

The Classic, Universal Mistakes

These show up in every SQL codebase regardless of which database engine sits underneath it, and they're worth covering properly before getting to the engine-specific ones, since skipping straight to the more novel material would leave out the mistakes that are actually most likely to be costing you time right now.

Using SELECT * instead of naming columns

SELECT * pulls every column even when you need two or three, wastes bandwidth, and silently changes behavior if someone adds a column to the table later. Naming columns explicitly is one line of extra typing that prevents a real category of bugs.

Running DELETE or UPDATE without a WHERE clause

This is the single most dangerous mistake on this list, because it can wipe out an entire table in one keystroke. Always test the equivalent SELECT statement with the same condition first, to see exactly which rows will be affected, before running the DELETE or UPDATE itself.

Comparing NULL with the equality operator

NULL represents an unknown value, so comparing anything to NULL with = returns unknown rather than true, which SQL treats as false. This trips up beginners and experienced developers alike, since it's one of the few places SQL's logic genuinely diverges from how most other languages handle missing values.

Leaving join columns unaliased

Table aliases aren't just style preference once a query joins three or more tables. Without them, a column name that exists in multiple tables becomes genuinely ambiguous, and some engines will throw an error while others will silently pick one, which is its own quiet source of bugs.

GROUP BY errors with non-aggregated columns

Selecting a column that isn't either aggregated (with COUNT, SUM, AVG, and similar) or included in the GROUP BY clause produces an error on strict engines like PostgreSQL, and an unpredictable, arbitrary value on more permissive engines like older MySQL configurations. Every non-aggregated column in the SELECT list needs to appear in the GROUP BY clause.

Using implicit joins instead of explicit JOIN syntax

Forgetting the WHERE condition entirely in the comma-style syntax silently produces a cartesian product, every row from the first table matched with every row from the second, rather than an error. Explicit JOIN syntax makes the join condition mandatory and impossible to accidentally omit.

Skipping LIMIT while exploring a large table

Running an unbounded SELECT against a table with millions of rows, just to see what the data looks like, wastes time and resources for no benefit. Adding LIMIT 10 or your engine's equivalent while exploring costs nothing and avoids pulling back far more data than you actually need to look at.

Missing indexes and non-SARGable queries

A SARGable (Search ARGument-able) query is one the database can actually use an index to answer. Wrapping a column in a function inside a WHERE clause, such as WHERE YEAR(order_date) = 2026, typically makes the query non-SARGable, forcing a full table scan even when an index exists on order_date, since the database has to evaluate the function against every row before it can compare the result. Rewriting it as a range condition, WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01', lets the index actually get used, since the comparison can be evaluated directly against the stored values without transforming them first.

SQL injection from unparameterized queries

Concatenating user input directly into a SQL string lets an attacker submit input that changes the query's meaning entirely. Parameterized queries or prepared statements ensure user input is always treated as data, never as executable SQL, regardless of what characters it contains. This isn't optional for any application handling user input.

The Mistake Nobody Mentions #1: NULL Sorting Order

Here's a query that looks completely unambiguous:

Customers who have never made a purchase have a NULL last_purchase_date. Where do they land in the result? The answer depends entirely on which database you're running.

The fix is straightforward once you know to look for it: never rely on the default. PostgreSQL, Oracle, and SQLite (as of version 3.30.0) support an explicit NULLS FIRST or NULLS LAST clause. MySQL and SQL Server do not support this syntax at all, so they need a workaround:

Consider what this means in practice for a report built on PostgreSQL and later migrated to run against MySQL, or a dashboard tool that connects to whichever database a client happens to use. A "customers by most recent purchase" report built assuming NULLs land at the bottom on the original engine would silently show never-purchased customers at the very top of the list on the other, with no error, no warning, and no obvious reason for anyone reviewing the output to suspect the sort order itself is the problem.

The Mistake Nobody Mentions #2: Case Sensitivity

The same uncertainty applies to comparing text. Ask yourself: does WHERE city = 'chicago' match a row where the city column contains "Chicago"? The honest answer is: it depends on the engine and its collation settings, and getting this wrong silently returns fewer rows than expected rather than throwing an error.

All four engines let you override their default with an explicit COLLATE clause, or by wrapping both sides of the comparison in LOWER() or UPPER(), though the latter approach can prevent the database from using a standard index unless a functional index is created specifically for it.

The practical failure mode here is subtler than the NULL sorting issue, since it doesn't just reorder results, it silently excludes rows a query should have matched. A lookup table where city names were entered inconsistently, "Chicago" in some records and "chicago" in others, will still return every matching row on MySQL or SQL Server, while a PostgreSQL or SQLite database will only return the exact case that was queried, undercounting the true result without any indication that rows were missed.

Quick Reference: Behavior by Engine

Notice there's no clean pattern connecting the two columns, an engine that treats NULL one way doesn't reliably predict how it handles case sensitivity, so memorizing "PostgreSQL-like" or "MySQL-like" behavior as a single bundle doesn't actually work. Each behavior needs to be checked independently for whichever engine you're targeting. If you're working across more than one of these engines, our PostgreSQL vs MySQL vs BigQuery vs Snowflake comparison covers the broader syntax and behavior differences between them in more depth.

How to Write Portable SQL

Not every query needs to run identically across five different engines, but if there's any chance yours will move between environments, whether that's a staging database with a different engine than production, a BI tool that lets end users pick their own data source, or a migration project down the line, a few habits prevent silent breakage rather than a loud, easy-to-catch error that would at least tell you something was wrong.

Common Mistakes When Debugging SQL

These are less about writing SQL and more about the habits that surround it, and they're just as responsible for wasted hours as any single bad query.

• Running untested queries directly against production. Test complex joins and updates against a staging copy or a LIMIT-restricted SELECT first.
• Assuming a query is slow because SQL is slow, rather than checking the execution plan. Most database tools can show you whether a query is using an index or falling back to a full table scan, which is almost always the real, fixable answer rather than a limitation of SQL itself.
• Fixing a query for one engine's quirks and assuming it now works everywhere. As this guide covers throughout, engine-specific behavior means a fix for MySQL's NULL handling doesn't automatically fix the same issue on PostgreSQL.
• Not checking for duplicate values in a join key before joining. Duplicate keys on either side of a join silently multiply row counts into a many-to-many match instead of the one-to-one relationship usually expected, and the resulting numbers can still look plausible enough that nobody questions them until much later.
• Trusting that a query which returns the right row count is actually correct. A wrong join or an incorrect filter can occasionally produce a coincidentally plausible row count while still returning the wrong rows entirely, which is why spot-checking actual values matters as much as checking totals.

SQL Interview Questions

A few of these specifically test whether a candidate understands SQL conceptually versus having memorized syntax for one particular engine, which is exactly the distinction this entire guide has been building toward.

• Why does WHERE column = NULL return no rows? A strong answer explains NULL as an unknown value rather than just stating the IS NULL fix.
• Where do NULL values sort by default in an ORDER BY? The correct, complete answer is "it depends on the database engine," not a single universal rule.
• What makes a query non-SARGable, and why does it matter? Naming a concrete example, like wrapping a date column in a function, demonstrates real understanding.
• How would you prevent SQL injection in an application? Parameterized queries or prepared statements is the expected answer, not input sanitization alone.

Our Data Science Interview Quiz includes a full SQL section if you want to practice questions like these in more depth.

Frequently Asked Questions

Conclusion: Some SQL Mistakes Are Really Engine Mistakes

Most of what makes SQL frustrating for newcomers has nothing to do with the language being poorly designed and everything to do with the assumption that "SQL" is one single, uniform thing shared identically across every database product. It isn't. NULL sorting and case sensitivity are two examples where the SQL standard left the behavior undefined, and every major engine filled that gap differently. The classic mistakes covered earlier, SELECT *, missing WHERE clauses, NULL comparisons, unaliased joins, implicit join syntax, will trip you up on any engine. The engine-specific behaviors will only trip you up when you least expect it, on the one deployment where the default happens to differ from what you tested locally, which is exactly why they're worth learning to check for deliberately rather than discovering them the hard way in production.

The engine-specific claims in this guide are drawn directly from PostgreSQL's own documentation, MySQL's own reference manual, and Microsoft's and AWS's own official technical documentation, cross-checked against each other rather than repeated as folklore. The classic mistakes draw on established explainers from across the SQL education community. This page was last updated in August 2026, and given that database engines occasionally revise their default behavior in major version releases, it's worth rechecking the specifics here if you're reading this well after that date.