Common Query Pitfalls & Anti-patterns
Overview
Query pitfalls are common mistakes that return incorrect row-sets or cause severe performance issues. Examples include accidental cross-joins, using SELECT * unnecessarily (which wastes network/memory and breaks applications if columns are added/removed), or applying functions on indexed columns which forces full table scans.
Writing SQL that 'works' on small datasets often masks underlying anti-patterns. Knowing these pitfalls prevents a production outage when data scales.
Where used: database optimization, peer code review
Why learn this
- Avoid performance cliffs caused by hidden table scans.
- Ensure your aggregations and joins return accurate row counts.
Query walkthrough
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders) ORDER BY id;Focus: Using IN with a subquery is valid, but can sometimes perform poorly compared to EXISTS or a JOIN on larger datasets. Here it filters to the 6 customers who have orders.
Common mistakes
- Missing JOIN conditions: Forgetting an ON clause or WHERE condition in a join creates a Cartesian product (cross join), returning every combination of rows. Fix: Always explicitly define how tables relate.
- Applying functions on indexed columns: Wrapping an indexed column in a function (like `WHERE YEAR(date) = 2023`) breaks the index because the database has to run the function on every row (breaking SARGability). Use `WHERE date >= '2023-01-01' AND date < '2024-01-01'` instead.
Glossary
- outage
- A period when a system or service becomes unavailable, often caused by severe performance issues or crashes.
- anti-patterns
- Commonly used solutions or coding practices that actually create more problems than they solve.
- Cartesian
- A mathematical term describing a result where every single item in one list is paired with every single item in another list.
Recall questions
- Why is it an anti-pattern to use `SELECT *` in application code?
Understanding checks
What rows does this return?
8
Counts all 8 customers. A common pitfall is counting a column that has NULLs when you mean to count all rows (like COUNT(*)).
What rows does this return?
7
Customer 5 (Emma) has a NULL country. COUNT(column) ignores NULL values, which is a frequent source of accidental bugs.
Practice tasks
Fixing the COUNT pitfall
Given this query that accidentally ignores NULL countries, change it to safely count all customer rows regardless of NULLs.
Challenge
Avoid SELECT *
Write a query that joins products and order_items for order_id 1, but instead of using SELECT *, explicitly select the product name and the quantity ordered.
Continue learning
Previous: Reading EXPLAIN ANALYZE