SELECT * FROM products WHERE category_id IN (SELECT id FROM categories); -- categories has NULL

SELECT * FROM products WHERE category_id IN (SELECT id FROM categories WHERE id IS NOT NULL);

Filter NULL from subquery results to avoid unexpected behavior with IN.

Always filter NULL from IN subqueries

High

IN with NULL in subquery can cause issues

SELECT * FROM orders o WHERE total > (SELECT AVG(total) FROM orders WHERE customer_id = o.customer_id); -- Correlated

WITH avg_by_customer AS (SELECT customer_id, AVG(total) AS avg_total FROM orders GROUP BY customer_id) SELECT o.* FROM orders o JOIN avg_by_customer a ON o.customer_id = a.customer_id WHERE o.total > a.avg_total;

Use CTE or JOIN to avoid correlated subquery. Much faster for large datasets.

Avoid correlated subqueries, use CTEs or JOINs

High

Correlated subquery evaluated per row (slow)