SQL Interview Prep · Lesson

Rewriting Correlated Subqueries as Joins

Flattening correlated logic into joins or window functions for performance.

Rewriting Correlated Subqueries as Joins is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

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;

The Fan-Out Pitfall

The most common rewrite bug is forgetting fan-out. EXISTS returns each customer once no matter how many orders they have. A naive join returns one row per order, inflating counts.

If a downstream step does COUNT(*) or SUM(amount) over that joined result without grouping carefully, the numbers will be wrong.

Always ask: can the join multiply rows? If yes, use DISTINCT or a GROUP BY to collapse back.

Pattern 2: NOT EXISTS to LEFT JOIN / IS NULL

The anti-join rewrite is a guaranteed interview pattern. A correlated NOT EXISTS becomes a LEFT JOIN where the right side is NULL.

Unmatched outer rows get NULLs on the right; filtering for that NULL keeps exactly the rows with no match.

-- Correlated NOT EXISTS
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o
                  WHERE o.customer_id = c.customer_id);

-- LEFT JOIN / IS NULL rewrite
SELECT c.customer_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.customer_id IS NULL;

Pick a NOT-NULL Column to Test

In the LEFT JOIN / IS NULL rewrite, test a right-side column that is never NULL on a real match, ideally the join key or primary key.

If you test a nullable column, you cannot tell a genuine non-match (no row) from a matched row that simply has NULL there. That bug returns wrong rows.

Using the join key (here o.customer_id) or o.order_id guarantees NULL means "no matching row."

Pattern 3: Scalar Aggregate to JOIN + GROUP BY

A correlated aggregate in SELECT can become a join against a grouped subquery (a derived table).

Compute the per-group aggregate once, then join it back to the detail rows. The inner query runs a single time instead of per row.

-- Correlated scalar aggregate
SELECT e1.name,
       (SELECT MAX(e2.salary) FROM employees e2
        WHERE e2.dept_id = e1.dept_id) AS dept_max
FROM employees e1;

-- Join + GROUP BY rewrite
SELECT e.name, m.dept_max
FROM employees e
JOIN (SELECT dept_id, MAX(salary) AS dept_max
      FROM employees GROUP BY dept_id) m
  ON m.dept_id = e.dept_id;

Pattern 4: The Window Function Rewrite

Often the cleanest rewrite is a window function. MAX(salary) OVER (PARTITION BY dept_id) replaces the correlated aggregate entirely, no join needed.

It computes the group value in a single pass and keeps every detail row. This is usually the answer interviewers most want to see for analytics queries.

SELECT name,
       dept_id,
       salary,
       MAX(salary) OVER (PARTITION BY dept_id) AS dept_max
FROM employees;

Greatest-N-Per-Group Rewrite

A correlated subquery selecting the top row per group (salary = MAX per dept) rewrites neatly with ROW_NUMBER.

Partition by the group, order by the metric, and keep rank 1. Use RANK instead if you want all tied top rows.

SELECT name, dept_id, salary
FROM (
    SELECT name, dept_id, salary,
           ROW_NUMBER() OVER (PARTITION BY dept_id
                              ORDER BY salary DESC) AS rn
    FROM employees
) t
WHERE rn = 1;

When NOT to Rewrite

Rewriting is not always a win. Keep the correlated subquery when:

The outer set is tiny, so per-row cost is negligible.
The correlated column is well indexed and the optimizer already turns it into an efficient semi-join.
Readability matters more than micro-optimization in maintained code.

Modern optimizers frequently transform EXISTS into a semi-join automatically. Say that you would measure with EXPLAIN before assuming a rewrite helps.

Verifying Equivalence

After any rewrite, confirm it returns the same rows and the same cardinality as the original.

Check row counts match.
Check no duplicates were introduced by a join fan-out.
Check NULL and empty-group edge cases still behave.

A quick way: run both versions and EXCEPT them both directions; an empty result means they agree. Interviewers value that you verify rather than assume.

SELECT customer_id FROM query_a
EXCEPT
SELECT customer_id FROM query_b;
-- and the reverse; both empty => equivalent

Rewriting IN to a JOIN

An uncorrelated IN subquery often rewrites to a join too, but the same fan-out warning applies. IN de-duplicates membership; a join does not.

If the inner list has duplicate keys, the join repeats outer rows. Use DISTINCT on the inner side or on the final result to match IN semantics.

-- IN subquery
SELECT c.name FROM customers c
WHERE c.customer_id IN (SELECT o.customer_id FROM orders o);

-- Join rewrite, de-duplicated to match IN
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id;

Quick Check

Choose the correct join rewrite for a correlated NOT EXISTS anti-join.

Recap: Rewriting Correlated Subqueries as Joins

Key takeaways:

EXISTS → INNER JOIN (add DISTINCT to avoid fan-out duplicates).
NOT EXISTS → LEFT JOIN ... WHERE key IS NULL (test a non-nullable column).
Correlated scalar aggregate → JOIN a grouped derived table, or better, a window function.
Top-per-group → ROW_NUMBER (or RANK for ties).
Verify equivalence and check with EXPLAIN before assuming a rewrite is faster.

Knowing both forms and the fan-out trap is exactly what mid-level interviews probe.

Frequently asked questions

Is the “Rewriting Correlated Subqueries as Joins” lesson free?

Yes — the full text of “Rewriting Correlated Subqueries as Joins” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Rewriting Correlated Subqueries as Joins”?

Flattening correlated logic into joins or window functions for performance. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Rewriting Correlated Subqueries as Joins” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

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

← Back to SQL Interview Prep