SQL query optimization is a critical skill for any developer working with databases. While many developers memorize rules like "never use IN, always use EXISTS," the reality is more nuanced. This article dives deep into common SQL anti-patterns involving IN, NOT IN, and missing indexes, explaining when they hurt performance, how NULL values can silently corrupt results, and how to rewrite queries for correctness and speed.
When IN Is Safe and When It's Not
The IN operator is perfectly fine for small, static sets — for example, filtering by a handful of status values:
SELECT * FROM orders
WHERE status IN ('PAID', 'SHIPPED', 'FINISHED');
Here, the database can efficiently evaluate the list. There's no need to rewrite this as EXISTS.
The problem arises when IN contains a subquery that returns many rows:
SELECT *
FROM customer
WHERE id IN (
SELECT customer_id
FROM orders
WHERE amount > 1000
);
If orders is large, the database must match every customer.id against potentially millions of customer_id values. Modern optimizers (e.g., MySQL) can convert this into a semijoin, but only if statistics, indexes, and query structure are favorable. You cannot rely on the optimizer to fix a poorly written query.
The EXISTS Advantage
For existence checks — "does this customer have any high-value orders?" — EXISTS is semantically clearer and often faster:
SELECT c.*
FROM customer c
WHERE EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
AND o.amount > 1000
);
EXISTS stops scanning as soon as it finds one matching row. With a proper index, this is far more efficient than IN for large subqueries.
Indexing for EXISTS
Create a covering composite index that supports both the join and the filter:
CREATE INDEX idx_orders_customer_amount
ON orders(customer_id, amount);
Without an index, neither IN nor EXISTS will save you from a full table scan.
The NOT IN NULL Trap
NOT IN is dangerous not because it's slow, but because it can return zero rows when the subquery contains a single NULL value.
Consider: find customers who have never placed an order.
-- WRONG: returns no rows if orders.customer_id has any NULL
SELECT *
FROM customer
WHERE id NOT IN (
SELECT customer_id
FROM orders
);
Why? In SQL, NULL means "unknown." The expression 1 NOT IN (2, 3, NULL) evaluates to UNKNOWN (not TRUE), because the database cannot guarantee that 1 is not equal to an unknown value. The WHERE clause only returns rows where the condition is TRUE, so the entire query yields nothing.
This is a silent data bug — no error, just wrong results.
Safer Alternative: NOT EXISTS
SELECT c.*
FROM customer c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
NOT EXISTS is unaffected by NULLs in the subquery. It checks each customer individually and returns those with no matching order.
JOIN Alternatives and Duplicate Handling
When you need columns from the related table, a JOIN may be more appropriate:
-- Customers with high-value orders (may produce duplicates)
SELECT DISTINCT c.*
FROM customer c
JOIN orders o ON o.customer_id = c.id
WHERE o.amount > 1000;
Note the DISTINCT — a customer with multiple qualifying orders would otherwise appear multiple times.
For anti-joins (records without a match), LEFT JOIN / IS NULL is a common pattern:
SELECT c.*
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.customer_id IS NULL;
This is logically equivalent to NOT EXISTS. Which one is faster? Always check with EXPLAIN.
Using EXPLAIN to Diagnose Performance
Never guess about performance. Use EXPLAIN (or EXPLAIN ANALYZE) to see the execution plan:
EXPLAIN
SELECT c.*
FROM customer c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
Look for:
- Index scans vs. full table scans
- Rows estimated vs. actual rows
- Using temporary or Using filesort (red flags)
- DEPENDENT SUBQUERY (often slow)
If you see a full table scan on a large table, add an index on the join column.
Common Pitfalls
- Using NOT IN with a subquery that may contain NULL — results become empty without error.
- Assuming the optimizer will fix a bad IN subquery — it may not, especially with skewed data.
- Forgetting DISTINCT when using JOIN for existence checks — duplicates slip in.
- Adding too many indexes — they speed up reads but slow down writes.
- Skipping EXPLAIN — optimization without data is guesswork.
FAQ
What's the difference between IN and EXISTS?
IN evaluates a list of values and checks if the outer value is present. EXISTS checks for the existence of at least one row in a subquery. For large subqueries, EXISTS is usually faster because it can short-circuit.
Why does NOT IN return no rows when there's a NULL?
Because NULL is not a value — it's unknown. x NOT IN (list) returns UNKNOWN if any element in the list is NULL, and the WHERE clause only keeps rows where the condition is TRUE.
Should I always replace IN with EXISTS?
No. For small static lists (e.g., IN ('A', 'B', 'C')), IN is clear and efficient. Reserve EXISTS for subqueries that may return many rows.
How do I choose between NOT EXISTS and LEFT JOIN / IS NULL?
Both are logically equivalent. Test with your data and EXPLAIN. Often they produce the same plan, but one may be faster depending on indexes and statistics.
What's the most important thing for SQL performance?
Indexes on join and filter columns. Without them, no rewrite of IN to EXISTS will save you from a full table scan. Always check EXPLAIN to confirm your query uses indexes.
Try it in our SQL formatter to clean up your query before analyzing it.