Common SQL Pitfalls

Most production SQL incidents don't come from exotic bugs — they come from a small set of well-known mistakes repeated by developers who haven't hit them yet. Forgetting a WHERE clause on an UPDATE, comparing against NULL with =, or letting an implicit type conversion silently disable an index are all mistakes that pass code review and pass local testing, then cause real damage at scale or in production. This topic walks through the pitfalls that come up again and again, explains precisely why each one is a problem, and shows the fix.

Without a WHERE clause, UPDATE and DELETE apply to every single row in the table, not just the ones you intended to change. This is one of the most common causes of real production incidents because the syntax is valid and the statement runs silently to completion. The safest habit is to always write and verify the WHERE clause first by running the equivalent SELECT with the same condition to confirm it returns exactly the intended rows, then convert it to UPDATE/DELETE. Many teams also enforce autocommit-off workflows or require explicit transactions with a review step before COMMIT on any bulk write against a production table.

NULL means 'unknown', and SQL's three-valued logic means any equality comparison involving NULL evaluates to UNKNOWN, not TRUE or FALSE. WHERE only keeps rows where the condition is TRUE, so UNKNOWN rows are dropped — even rows where the column genuinely is NULL. The query runs without error and simply returns zero matching rows for that condition, which is far more dangerous than a syntax error because it's easy to miss. The fix is always to use IS NULL or IS NOT NULL for NULL checks, never = or !=.

SELECT * pulls every column regardless of whether the application needs it, increasing network I/O and memory usage, and it can prevent the query optimizer from using a covering index (an index containing only the needed columns), forcing extra lookups to the full row. It also creates a hidden coupling: if someone adds a new column or reorders columns in the table, application code relying on positional access or expecting a fixed column set can break unexpectedly, and a query that used to be index-covered can silently become slower after a schema change. The fix is to always name the exact columns you need.

N+1 happens when code runs one query to fetch a list of N parent rows, then loops over the results running a separate query per row to fetch related child data — resulting in 1 + N total round trips to the database instead of one or two. This is common with ORMs that lazily load related objects inside a loop. Each round trip carries network latency overhead, so what should be a handful of queries becomes hundreds or thousands as data grows. The fix is to use a JOIN, or an ORM's eager-loading/batch-loading feature, or a single query with IN (...) to fetch all related rows at once.

A foreign key constraint guarantees referential integrity, but in most databases it does not automatically create an index on the referencing column. Without an index, every JOIN on that foreign key, and every check the database performs when a referenced row is updated or deleted (to see if child rows exist), requires a full table scan of the child table. As tables grow, this turns cheap operations into expensive ones and is a very common, easy-to-miss cause of slow joins and slow cascading deletes. The fix is simple: explicitly create an index on every foreign key column that is queried or joined on.

If a column is stored as one type (say, a VARCHAR) but compared against a different type in a query (say, an unquoted number, or a function wrapped around the column like YEAR(date_column) = 2024), the database may need to convert every stored value to match before it can compare — which means it can no longer use a standard index lookup and falls back to scanning every row. This often happens subtly, such as comparing an indexed integer ID column to a string parameter passed from application code, or wrapping an indexed date column in a function. The fix is to keep comparison types consistent with the column's native type and avoid wrapping indexed columns in functions in WHERE clauses; instead, rewrite the condition to leave the column bare, e.g., date_column >= '2024-01-01' AND date_column < '2025-01-01' instead of YEAR(date_column) = 2024.

Every index speeds up reads that use it but adds overhead to every INSERT, UPDATE, and DELETE, since each index must also be updated to stay consistent with the table. Adding indexes 'just in case' without checking whether queries actually use them wastes storage and slows down write-heavy workloads. The fix is to index based on actual query patterns — commonly filtered, joined, or sorted columns — and periodically review index usage statistics to drop indexes that are never used.

When related writes (for example, debiting one account and crediting another) are executed as separate statements without a transaction, a failure or crash between them can leave the database in an inconsistent state — money debited but never credited, for instance. Wrapping related statements in BEGIN...COMMIT (with ROLLBACK on failure) ensures the changes are applied atomically: either all of them succeed or none do. Skipping this is a common pitfall in application code that treats each statement as independent instead of as part of one logical operation.

Building queries by concatenating raw user input directly into SQL strings opens the door to SQL injection, where an attacker crafts input that changes the query's logic entirely (for example, appending OR '1'='1' to bypass a WHERE condition, or chaining a second malicious statement). Beyond security, concatenation also breaks query plan caching because the database sees a structurally different query string every time. The fix is to always use parameterized queries or prepared statements, which separate the query structure from the data values and let the database driver handle escaping safely.

Sorting a large result set is expensive, and if the application only displays the first page of results anyway, sorting and returning the entire dataset wastes both database and network resources. Pagination with LIMIT/OFFSET (or better, keyset pagination using a WHERE condition on the last seen value) lets the database avoid materializing and transferring rows the application will never use. This pitfall is common in list/search endpoints that grow slow as the underlying table grows, even though the query worked fine during development with a small dataset.