SQL Interview Prep · Lesson

Correlated EXISTS and NOT EXISTS

The robust anti-join alternative that handles NULLs correctly.

Correlated EXISTS and NOT EXISTS is a free SQL Interview Prep lesson on CoddyKit — lesson 3 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.

EXISTS Tests for Presence

EXISTS takes a subquery and returns TRUE as soon as that subquery yields at least one row, otherwise FALSE. It never returns the rows themselves.

With a correlated subquery inside, EXISTS becomes a per-outer-row presence test: "does a matching row exist for this outer row?"

Because it short-circuits on the first match, it does not care how many rows match. That semantics detail is a favorite interview point.

A Basic Correlated EXISTS

Find customers who have placed at least one order. The inner query is correlated through o.customer_id = c.customer_id.

For each customer, EXISTS asks: is there any order for this customer? If yes, keep the customer.

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Why SELECT 1 Inside EXISTS

You will see SELECT 1, SELECT *, or SELECT NULL inside EXISTS. They are all equivalent.

EXISTS only checks whether rows come back, never their contents, so the projected columns are irrelevant. The optimizer ignores them.

SELECT 1 is a common convention that signals intent: "I only care about existence." Pick one and be consistent; do not let an interviewer think the column list matters here.

NOT EXISTS Finds the Missing

NOT EXISTS flips the test: keep the outer row only when the correlated subquery returns no rows.

This is the canonical anti-join: customers with no orders, products never sold, students with no submissions.

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

The NOT IN NULL Trap

Here is the interview gold. NOT IN against a subquery that can contain NULL behaves treacherously: if the list has even one NULL, NOT IN returns no rows at all.

That happens because comparing to NULL yields UNKNOWN, and NOT IN requires every comparison to be false. One UNKNOWN poisons the whole condition.

NOT EXISTS does not suffer this; it simply checks row presence and handles NULLs safely.

-- Risky: returns nothing if any o.customer_id is NULL
SELECT c.customer_id FROM customers c
WHERE c.customer_id NOT IN (SELECT o.customer_id FROM orders o);

-- Safe: NULLs do not break it
SELECT c.customer_id FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Why NOT EXISTS Is NULL-Safe

The reason is in the matching logic. NOT EXISTS checks whether any inner row satisfies o.customer_id = c.customer_id.

A row where o.customer_id is NULL never satisfies that equality (NULL = anything is UNKNOWN, not TRUE), so it simply does not count as a match. The presence test stays correct.

With NOT IN, that same NULL becomes part of a list comparison whose UNKNOWN result wipes out all output. This is why senior screens prefer NOT EXISTS.

EXISTS With Extra Conditions

The correlated subquery can carry more predicates. Find customers who placed at least one order over 1000.

The extra condition lives inside the EXISTS subquery, scoped per customer.

SELECT c.customer_id, c.name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
      AND o.amount > 1000
);

Performance: Short-Circuit Behavior

EXISTS can stop scanning the inner relation as soon as one matching row is found. It does not build or count a full result set.

That makes EXISTS typically efficient, especially when the correlated column is indexed, because each per-row probe can find a match quickly and bail out.

Contrast with a correlated COUNT(*) > 0, which forces counting every match. Prefer EXISTS when you only need a yes/no answer.

EXISTS vs COUNT for Presence

Candidates sometimes write a correlated count to test presence. It works but wastes effort.

The COUNT version tallies every matching order; EXISTS quits after the first. For a pure existence test, EXISTS communicates intent and lets the optimizer short-circuit.

-- Works but counts everything
SELECT c.customer_id FROM customers c
WHERE (SELECT COUNT(*) FROM orders o
       WHERE o.customer_id = c.customer_id) > 0;

-- Better: stops at first match
SELECT c.customer_id FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o
              WHERE o.customer_id = c.customer_id);

Worked Example: Products Never Ordered

A classic anti-join interview prompt: list products that have never been ordered. NOT EXISTS reads almost like the English requirement.

For each product, check whether any order line references it; keep only those with none.

SELECT p.product_id, p.name
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM order_items oi
    WHERE oi.product_id = p.product_id
);

EXISTS in NOT EXISTS for Divide-Style Queries

Nesting EXISTS inside NOT EXISTS expresses relational division: "find rows that match ALL of a set." A classic prompt is "customers who ordered every product in a category."

The logic: keep a customer when there is no product they have not ordered. That double negative is the hallmark of a division query, and interviewers use it to test deep EXISTS fluency.

SELECT c.customer_id
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM products p
    WHERE p.category = 'Coffee'
      AND NOT EXISTS (
          SELECT 1 FROM order_items oi
          JOIN orders o ON o.order_id = oi.order_id
          WHERE oi.product_id = p.product_id
            AND o.customer_id = c.customer_id
      )
);

Quick Check

Choose the safest way to find customers with no orders.

Recap: Correlated EXISTS and NOT EXISTS

Key takeaways:

EXISTS is a per-row presence test that short-circuits on the first match; column choice inside is irrelevant (use SELECT 1).
NOT EXISTS is the NULL-safe anti-join for finding rows with no match.
NOT IN with a NULL in the list returns nothing; prefer NOT EXISTS.
For existence, EXISTS beats a correlated COUNT(*) > 0 because it stops early.

Mention the NOT IN NULL trap unprompted; it is a reliable signal of SQL maturity.

Frequently asked questions

Is the “Correlated EXISTS and NOT EXISTS” lesson free?

Yes — the full text of “Correlated EXISTS and NOT EXISTS” 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 “Correlated EXISTS and NOT EXISTS”?

The robust anti-join alternative that handles NULLs correctly. 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 3 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Correlated EXISTS and NOT EXISTS” 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