Rewriting Correlated Subqueries as Joins
Flattening correlated logic into joins or window functions for performance.
Why Rewrite at All
Correlated subqueries are readable but can be slow: the inner query may run once per outer row. Interviewers often ask you to rewrite one as a join or window function to improve performance.
The goal is the same result with a single pass over the data instead of repeated inner scans.
Knowing two or three rewrite patterns, and when each preserves correctness, is a core mid-level skill.
Pattern 1: EXISTS to INNER JOIN
A correlated EXISTS that tests for at least one match can often become an INNER JOIN.
But beware: a join can produce duplicate outer rows if multiple inner rows match. Add DISTINCT or aggregate to restore one row per outer key.
-- Correlated EXISTS
SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id);
-- Join rewrite (DISTINCT avoids dupes from fan-out)
SELECT DISTINCT c.customer_id, c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;All lessons in this course
- Anatomy of a Correlated Subquery
- Per-Group Aggregates Without GROUP BY
- Correlated EXISTS and NOT EXISTS
- Rewriting Correlated Subqueries as Joins