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

The most common SQL mistakes beginners make are using SELECT *, forgetting WHERE clauses, mixing up NULL handling, not using aliases, and writing inefficient subqueries. These mistakes cause performance problems, incorrect results, and frustrated developers. Here are the 10 most frequent mistakes and exactly how to fix them.

Every batch of learners makes the same mistakes. These are not stupid mistakes, they are natural ones. SQL has quirks that are not obvious until someone points them out. Let me point them out.

1. Using SELECT * in Production Code

SELECT * is fine for exploring a table. It is terrible for production code. Here is why:

• It returns columns you do not need, wasting bandwidth and memory
• It breaks your application when someone adds a column
• It prevents MySQL from using covering indexes
• It makes your code unclear about what data it actually needs

The performance impact grows with table size. On a table with 50 columns and 1 million rows, SELECT * transfers 50 times more data than a query selecting just 5 columns. The extra data goes over the network, through your application layer, and into memory where it is never used. Over time, this waste adds up and affects your entire system.

Instead, specify the columns you need:

-- Bad
SELECT * FROM users;

-- Good
SELECT id, name, email FROM users;

A common scenario: a query using SELECT * on a table with 50 columns. The application only needs 3. When someone adds a TEXT column with 10MB per row, the query starts transferring 10MB instead of a few kilobytes. The application times out.

2. Forgetting the WHERE Clause

This mistake has deleted production data at companies worldwide. Running an UPDATE or DELETE without a WHERE clause affects every row in the table.

-- Dangerous
UPDATE users SET status = 'inactive';
DELETE FROM orders;

-- Safe
UPDATE users SET status = 'inactive' WHERE last_login < '2026-01-01';
DELETE FROM orders WHERE created_at < '2025-01-01';

Some MySQL clients have a safety feature that prevents UPDATE/DELETE without WHERE. Enable it. In mysql CLI, use --safe-updates. In Workbench, go to Preferences and enable "Safe Updates."

The safest approach is to always run your UPDATE or DELETE as a SELECT first. Replace the keyword and check the results. This way you can see exactly which rows will be affected before any data is changed. It takes ten seconds and can save you from a catastrophic mistake.

3. Comparing with NULL Using =

NULL is not a value, it is the absence of a value. You cannot compare NULL with =. You need IS NULL or IS NOT NULL.

-- Wrong (returns no rows)
SELECT * FROM users WHERE email = NULL;

-- Correct
SELECT * FROM users WHERE email IS NULL;

-- Also correct
SELECT * FROM users WHERE email IS NOT NULL;

This catches everyone at least once. The query does not throw an error, it just returns an empty result set. You spend 20 minutes debugging your application before realizing the problem is a single equals sign. The same rule applies to comparisons with NULL in JOIN conditions and IN clauses. Any time NULL might be present, think about how your comparison will behave.

4. Not Using Aliases for Readability

Table aliases are not just for shortening users u. They make your queries readable and maintainable. When you have JOINs, aliases are essential.

-- Hard to read
SELECT users.name, orders.total, products.name
FROM users
JOIN orders ON users.id = orders.user_id
JOIN order_items ON orders.id = order_items.order_id
JOIN products ON order_items.product_id = products.id;

-- Clear and readable
SELECT u.name, o.total, p.name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

Use aliases for every table in a JOIN. It makes the query easier to read and easier to modify later.

5. Using OR When You Should Use IN

IN is almost always better than multiple OR conditions. It is more readable, easier to maintain, and sometimes faster.

-- Bad
SELECT * FROM products WHERE category = 'electronics' OR category = 'clothing' OR category = 'books';

-- Good
SELECT * FROM products WHERE category IN ('electronics', 'clothing', 'books');

If the list comes from a subquery, use IN or EXISTS:

-- Good
SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

6. Writing Subqueries When JOINs Would Be Better

Subqueries are not bad, but they are often slower than equivalent JOINs. MySQL's query optimizer has gotten better at converting subqueries to JOINs, but it is not perfect.

-- Slower (correlated subquery)
SELECT * FROM orders o
WHERE o.total > (SELECT AVG(total) FROM orders WHERE user_id = o.user_id);

-- Faster (JOIN)
SELECT o.* FROM orders o
JOIN (SELECT user_id, AVG(total) as avg_total FROM orders GROUP BY user_id) avg_orders
ON o.user_id = avg_orders.user_id
WHERE o.total > avg_orders.avg_total;

Sometimes subqueries are more readable. If performance is not critical and the subquery makes the logic clearer, use the subquery. Premature optimization is the root of all evil.

7. Not Indexing Columns Used in WHERE

If you frequently query WHERE email = 'something', you need an index on the email column. Without it, MySQL scans every row in the table.

-- Add an index for frequently queried columns
CREATE INDEX idx_users_email ON users(email);

-- For composite queries
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

The rule of thumb: index columns that appear in WHERE, JOIN ON, and ORDER BY clauses. Do not index every column, that wastes space and slows down writes.

8. Ignoring EXPLAIN

EXPLAIN shows you how MySQL executes your query. If a query is slow, run EXPLAIN first. It will show you which indexes are being used, how many rows are being scanned, and where the bottleneck is.

EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

Look for these red flags:

• type: ALL - Full table scan, add an index
• rows: 1000000 - Scanning a million rows for a simple lookup
• Extra: Using filesort - MySQL is sorting results in memory, add an index on the ORDER BY column
• Extra: Using temporary - MySQL is creating a temporary table, often caused by GROUP BY without an index

Make EXPLAIN a reflex, not an afterthought. Every query you write should be checked against EXPLAIN before you consider it done. This habit pays for itself the first time it catches a full table scan on a production table with millions of rows. Building this muscle memory early will separate you from developers who only debug performance after customers complain.

9. Not Using LIMIT for Testing

When you are writing a new query, always add LIMIT 10 while testing. If the query has a bug, it is easier to spot with 10 rows than with a million rows. It is also faster.

-- Test with LIMIT first
SELECT * FROM orders WHERE status = 'pending' LIMIT 10;

-- Remove LIMIT when you are confident the query is correct
SELECT * FROM orders WHERE status = 'pending';

10. Forgetting About Transaction Isolation

If two users try to update the same row at the same time, you need to handle it. Without proper transaction isolation, you can lose updates or read inconsistent data.

-- Bad (no transaction)
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Good (with transaction)
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

MySQL's default isolation level is REPEATABLE READ. For most applications, this is fine. If you need stricter isolation, use SERIALIZABLE. But be aware that stricter isolation means lower performance.

FAQ

What is the most common SQL mistake?

Using SELECT * in production code. It is the most common and the most harmful. It wastes resources, breaks applications when schemas change, and prevents index optimization.

How do I learn to write better SQL?

Practice. Use EXPLAIN on every query you write. Read the MySQL documentation. Look at how your ORM generates SQL and understand what it is doing.

Are these mistakes specific to MySQL?

No, most of these mistakes apply to all SQL databases. NULL handling, SELECT *, and missing indexes are universal problems.

Real-World Scenario: A Query That Got Worse Over Time

A developer wrote a query to find inactive users. It worked fine during development with 500 rows. Six months later, the table had 2 million rows and the query took 30 seconds. The developer had used SELECT *, a missing index, and a correlated subquery, all in one query.

-- The original slow query
SELECT * FROM users u
WHERE u.id IN (
  SELECT user_id FROM orders o
  WHERE o.total > (SELECT AVG(total) FROM orders WHERE user_id = o.user_id)
)
AND u.last_login < DATE_SUB(NOW(), INTERVAL 90 DAY);

The fix involved three changes. First, replace SELECT * with the specific columns the application needed. Second, add an index on orders(user_id, total) to speed up the subquery. Third, rewrite the correlated subquery as a JOIN.

-- The optimized query
SELECT u.id, u.name, u.email
FROM users u
JOIN (
  SELECT user_id, AVG(total) as avg_total
  FROM orders
  GROUP BY user_id
  HAVING AVG(total) > 0
) avg_orders ON u.id = avg_orders.user_id
JOIN orders o ON u.id = o.user_id AND o.total > avg_orders.avg_total
WHERE u.last_login < DATE_SUB(NOW(), INTERVAL 90 DAY)
GROUP BY u.id;

In this specific case, the query went from 30 seconds to 0.08 seconds. Each individual fix helped, but the combination of all three made the difference. The lesson is that small mistakes compound. One SELECT * adds a few milliseconds. One missing index adds a few seconds. One correlated subquery multiplies the damage. Fix them all at once.

Key Takeaways

1. Run EXPLAIN and verify no full table scans (type: ALL)
2. Confirm all WHERE and JOIN columns have indexes
3. Replace SELECT * with explicit column names
4. Test with LIMIT 10 before running on the full dataset
5. Wrap related writes in a transaction
6. Check for NULL comparisons using IS NULL, not = NULL

Keep this checklist visible during development. It takes 30 seconds to run EXPLAIN and can save hours of debugging later. Make it a habit, not an afterthought.