Common Mistakes
Pitfalls to avoid in SQL
These common mistakes trip up beginners and experts alike.
1. NULL Comparisons
-- WRONG: This never returns rows! SELECT * FROM users WHERE phone = NULL; -- CORRECT SELECT * FROM users WHERE phone IS NULL;
2. Forgetting GROUP BY Columns
-- ERROR: name is not in GROUP BY or aggregate SELECT country, name, COUNT(*) FROM users GROUP BY country; -- CORRECT: Include name in GROUP BY SELECT country, name, COUNT(*) FROM users GROUP BY country, name;
3. Cartesian Products
-- DANGEROUS: Missing JOIN condition creates explosion SELECT * FROM users, orders; -- Every user x every order! -- CORRECT: Always include JOIN condition SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
4. Not Using Transactions
-- DANGEROUS: Partial updates if error occurs UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- SAFE: Use transaction BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE id = 1; UPDATE accounts SET balance = balance + 100 WHERE id = 2; COMMIT;
5. SELECT * in Production
-- BAD: Returns unnecessary data, breaks if schema changes SELECT * FROM users; -- GOOD: Explicit columns SELECT id, name, email FROM users;