5 Common SQL Mistakes and How to Fix Them

Most SQL bugs do not announce themselves. A query returns the right answer in development, then crawls in production, or silently misses rows because of a NULL comparison. The language rewards careful thinking: be explicit about columns, indexes, joins, and nulls. These five mistakes show up again and again in code reviews. The fixes are not exotic, but they change how your queries behave under real data.

1. Reaching for SELECT * out of habit

SELECT * is fine when you are exploring a new table in a scratch query. It becomes a problem when it ships in application code. The database must read every column, return every column over the network, and hand every column to your application. If the table has a large text or JSON column, you pay for data you never use. You also lose the protection of a covering index: an index on (customer_id, created_at) cannot satisfy a query that asks for all columns, so the database may fall back to reading the table. And when someone adds a column, your code receives a field it did not expect. Name the columns you need. It costs a few seconds now and saves debugging later.

Fix: Replace SELECT * with an explicit list. Instead of SELECT * FROM orders WHERE customer_id = 42, write SELECT id, total, created_at FROM orders WHERE customer_id = 42. In application queries, make the explicit column list part of your standard review checklist.

• It reduces I/O and network transfer, especially for wide tables.
• It lets the database use covering indexes more often.
• It documents which fields the code actually depends on.
• It prevents new columns from leaking into APIs or logs.

2. Missing or misused indexes

An index is not a magic switch. It is a structure that helps the database find rows without scanning the whole table, but it only helps when the query can use it. A common mistake is indexing a column that is rarely filtered, while ignoring the columns used in joins, WHERE clauses, and ORDER BY. Another is wrapping an indexed column in a function, such as WHERE YEAR(created_at) = 2024, which usually prevents a normal index on created_at from being used.

How to choose an index

1. Find the slow query and look at its execution plan. Every major database can show you where time is spent.
2. List the columns used for equality filters, range filters, joins, and sorting.
3. Create a composite index with the most selective equality columns first, followed by sort or range columns.
4. Test the query again and compare the plan and timing. Remove indexes that are not used.
5. Remember that indexes speed up reads but slow down inserts, updates, and deletes. Do not index every column.

Foreign keys deserve attention too. Many databases do not create an index automatically for a foreign key column, yet that column is often used in joins. Check the columns that connect your tables and index them when the workload calls for it.

3. Poor joins and accidental cartesian products

A missing join condition can turn a harmless query into a cartesian product. If you write FROM orders, customers WHERE orders.customer_id = customers.id, you are using an older style that makes mistakes easy. Forget the WHERE clause and every order is paired with every customer. The result set may be huge, and the numbers may look plausible enough to pass a quick glance. That is why explicit joins are safer.

Use INNER JOIN, LEFT JOIN, and ON deliberately. Know whether you want to keep rows with no match. A LEFT JOIN followed by a WHERE condition on the right table can quietly behave like an inner join if you are not careful. Filter early when you can, and check row counts before and after each join. If a join multiplies rows, decide whether you need DISTINCT, a subquery, or an aggregate. Do not use DISTINCT to hide a fanout you do not understand.

Join habits that hold up

• Use explicit JOIN ... ON syntax instead of comma joins.
• Alias tables and qualify every column, especially when names overlap.
• Check whether the join column is indexed on both sides.
• Use EXISTS or IN for semi-joins when you only need to test for a match.
• Aggregate before joining when joining a detail table would duplicate rows.

4. Misunderstanding NULL

NULL is not zero, an empty string, or false. It means unknown. That difference matters because comparisons with NULL do not return true or false; they return unknown. Writing WHERE status = NULL will not find rows with a null status. You need WHERE status IS NULL. Similarly, WHERE status <> 'cancelled' will exclude rows where status is null, because the comparison is unknown. That can silently drop records from reports.

NOT IN is another trap. If the list contains a NULL, the result may be no rows at all. When you need to exclude values, NOT EXISTS is often clearer and safer. Also know the difference between COUNT(*), which counts rows, and COUNT(column), which counts non-null values. Use COALESCE when you need a fallback value for display or calculation, but do not use it to hide missing data in a way that changes business meaning.

5. Row-by-row thinking

SQL works best with sets, not loops. A common application pattern is the N+1 query: fetch a list of orders, then run one more query for each order to get its customer. That is fine with ten rows and painful with ten thousand. The database is built to join, filter, and aggregate in one pass, so let it. Fetch related data with a join, a subquery, or a single IN query using a batch of IDs.

Look for loops around database calls in your code. Replace them with bulk operations where possible. For inserts, use multi-row inserts or a bulk loader. For updates, use a single statement with a CASE expression or a temporary table. For pagination, OFFSET gets slower as the page number grows; keyset pagination using WHERE id > last_id ORDER BY id LIMIT 50 is often faster and more stable.

Keeping SQL clean in review

Good SQL is not about memorizing tricks. It is about being explicit and testing with realistic data. Before you merge a query, ask a few simple questions: Are the columns named? Are the join conditions complete? Are the filter columns indexed? Have you handled nulls on purpose? Could this run once instead of once per row? A short review can catch the mistakes that are hardest to see when the query returns a correct-looking result.

• Read the execution plan for any query that touches a large table.
• Test with nulls, duplicates, and empty results, not just the happy path.
• Keep queries readable with consistent formatting and meaningful aliases.
• Measure before and after changes; do not assume an index helped.
• Document unusual decisions so the next person understands the trade-off.

Start with one fix this week. Replace the SELECT * in a hot endpoint, add the missing index that shows up in the plan, or rewrite the join that returns more rows than expected. Small corrections compound into a codebase that is faster, easier to debug, and kinder to the next developer.

Photo: panumas nikhomkhai / Pexels