SQL has an unusual property: beginner code and expert code often look similar, but behave very differently at scale. A query that returns the right answer against a hundred test rows can quietly return the wrong answer, or take ninety seconds, against a million. This article covers the mistakes that cause that gap, why each one happens, and what to write instead.
1. Misunderstanding how NULL behaves
NULL is not zero and not an empty string. It means “unknown”, and comparisons with unknown values produce unknown results rather than true or false.
-- Returns nothing, ever
SELECT * FROM users WHERE deleted_at = NULL;
-- Correct
SELECT * FROM users WHERE deleted_at IS NULL;The consequences spread further than most beginners expect. A WHERE status != 'active' filter silently excludes every row where status is NULL, because NULL != 'active' is unknown, not true. If you want those rows, say so:
WHERE status IS DISTINCT FROM 'active'
-- or
WHERE status != 'active' OR status IS NULLAggregates ignore NULLs too. COUNT(column) counts non-null values while COUNT(*) counts rows, and AVG divides by the number of non-null values, not the number of rows. Neither is wrong, but the two answer different questions.
2. Using SELECT * in application code
It is convenient while exploring and a liability once shipped. It transfers columns you do not need, prevents the database from satisfying the query from an index alone, and — the real danger — breaks silently when someone adds or reorders a column. Code that relies on positional access, or an INSERT INTO ... SELECT *, will misbehave the day the schema changes.
Name your columns. It takes fifteen extra seconds and documents your intent for the next reader.
3. Confusing WHERE and HAVING
These run at different stages. WHERE filters rows before grouping; HAVING filters groups after aggregation.
-- Wrong: aggregate not available yet
SELECT customer_id, SUM(total)
FROM orders
WHERE SUM(total) > 1000
GROUP BY customer_id;
-- Correct
SELECT customer_id, SUM(total)
FROM orders
GROUP BY customer_id
HAVING SUM(total) > 1000;There is also a performance angle. Filtering that can happen in WHERE should happen there, because it reduces the number of rows the grouping operation has to process. Pushing a non-aggregate condition into HAVING produces the same result more slowly.
4. Accidental cross joins
Omit the join condition and the database pairs every row of one table with every row of the other. Ten thousand rows joined to ten thousand rows is a hundred million.
-- Cartesian product
SELECT * FROM orders, customers;
-- Explicit and safe
SELECT o.id, c.name
FROM orders o
JOIN customers c ON c.id = o.customer_id;Always use explicit JOIN ... ON syntax rather than comma-separated tables. It separates join conditions from filter conditions, which makes both easier to read, and a missing ON clause is a visible mistake rather than an invisible one.
5. Filtering a LEFT JOIN in the WHERE clause
This one produces wrong answers rather than errors, which makes it the most dangerous item on the list.
-- Silently becomes an INNER JOIN
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped';The LEFT JOIN dutifully includes customers with no orders, filling their order columns with NULL. Then the WHERE clause tests NULL = 'shipped', which is not true, and removes them again. The query now means the opposite of what the author intended.
-- Condition belongs in the join
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id AND o.status = 'shipped';The rule: conditions on the right-hand table of an outer join belong in ON. Conditions on the left-hand table belong in WHERE.
6. Wrapping indexed columns in functions
An index on created_at stores the raw values. Apply a function to the column and the index becomes unusable, forcing a full scan.
-- Cannot use an index on created_at
SELECT * FROM orders WHERE YEAR(created_at) = 2026;
-- Can
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
AND created_at < '2027-01-01';The same applies to LOWER(email) = ..., to string concatenation, and to arithmetic on the column. Rewrite the condition so the bare column sits on one side of the comparison, or create an expression index if the pattern is unavoidable.
A related case: a leading wildcard in LIKE '%term' cannot use a standard B-tree index either, because the index is ordered by the start of the string. Full-text search exists for that problem.
7. Ignoring the execution plan
Most developers guess at why a query is slow. The database will tell you if you ask.
EXPLAIN ANALYZE
SELECT ... ;You do not need to understand every line. Three things carry most of the signal: whether a sequential scan appears on a large table, whether estimated row counts differ wildly from actual ones (which usually means stale statistics), and where the time is actually spent. Optimising the part you assumed was slow, rather than the part the plan identifies, is the most common wasted afternoon in database work.
8. The N+1 query problem
This one usually arrives through an ORM rather than hand-written SQL. You fetch a list of posts, then loop over them fetching each author. One hundred posts becomes one hundred and one round trips to the database.
Each individual query is fast, which is why it never shows up in a slow-query log. The damage is in the network latency multiplied by the row count. The fix is to fetch related data in one query with a join, or to use your ORM’s eager-loading mechanism, which issues a second query with an IN clause rather than one per row.
9. Building queries by string concatenation
-- SQL injection waiting to happen
query = "SELECT * FROM users WHERE email = '" + userInput + "'"An input of ' OR '1'='1 returns every user. Worse inputs do worse things. Use parameterised queries, where the driver sends the SQL and the values separately and the value is never parsed as code:
SELECT * FROM users WHERE email = $1;This is not an advanced security technique. It is the ordinary way to write a query, and manual escaping is the deviation. Note that parameters work for values, not for identifiers — a dynamic table or column name must be validated against a whitelist instead.
10. UPDATE and DELETE without a WHERE clause
Everyone does it once. The habit that prevents it: write the statement as a SELECT first, confirm the row count is what you expect, then change the verb. On a production database, wrap it in a transaction so you have an escape route:
BEGIN;
DELETE FROM orders WHERE created_at < '2020-01-01';
-- check the reported row count
COMMIT; -- or ROLLBACK;11. Choosing the wrong data types
Storing dates as text, money as floating point, or booleans as varchar causes problems that surface much later.
Floating point is the worst offender because it is subtly wrong rather than obviously wrong. 0.1 + 0.2 does not equal 0.3 in binary floating point, and financial totals drift by fractions of a cent until someone notices a reconciliation failure. Use DECIMAL or NUMERIC for money, or store integer minor units.
Dates stored as strings cannot be compared, sorted or intervalled correctly, and force a function call on every query. Use a real date or timestamp type, and prefer a timezone-aware type if your users are not all in one place.
12. Indexing everything, or nothing
Indexes speed up reads and slow down writes, because every insert and update must maintain them. A table with fifteen indexes has a genuinely slow write path.
Index the columns you filter, join and sort on. Consider the column order in a composite index carefully: an index on (customer_id, created_at) helps a query filtering on customer_id alone, but an index on (created_at, customer_id) does not, because the index is ordered by its first column.
Then check which indexes are actually being used. Most databases expose usage statistics, and unused indexes are pure write overhead.
Frequently asked questions
Does the order of conditions in a WHERE clause affect performance?
Generally no. The query planner reorders conditions based on its statistics, not on the order you wrote them. Write them in the order that reads most clearly.
Is a subquery slower than a JOIN?
Often they compile to the same plan, so the honest answer is to check with EXPLAIN rather than assume. Correlated subqueries that run once per outer row are a genuine performance risk; a simple IN (SELECT ...) usually is not.
Should business logic live in the database?
Opinions differ sharply. Constraints, foreign keys and uniqueness rules belong in the database, because they protect data integrity regardless of which application writes to it. Complex procedural logic in stored procedures is harder to version, test and deploy, and most teams keep it in application code.
How do I practise without a real dataset?
Generate one. Most databases can produce millions of synthetic rows in a few statements, and the lessons in this article only become visible at that scale. Testing against twenty rows teaches you nothing about performance.
Conclusion
Most SQL problems fall into two families: queries that are wrong in a way that only appears with certain data, and queries that are correct but scale badly. NULL handling and outer-join filtering cause the first; function-wrapped columns, missing indexes and N+1 patterns cause the second. Learn to read an execution plan, always parameterise inputs, and test against realistic data volumes. Those three habits will catch nearly everything on this list before it reaches production.
