Lesson 35 of 61

5 mins read

SQL EXISTS & NOT EXISTS: Correlated Subqueries

In Plain English

The `EXISTS` operator tests whether a subquery returns ANY rows, evaluating to `TRUE` the instant a single matching row is encountered (short-circuit execution). `NOT EXISTS` verifies that no matching rows exist. Unlike `NOT IN`, `NOT EXISTS` handles NULL values safely and predictably.

The Mental Model

"`EXISTS` is like peering through a peephole into a room: the moment you see at least one person inside, you immediately say "Yes" without needing to count every single person in the room."

Deep Dive: How It Works

Correlated Subquery: The inner query references a column from the outer query (`WHERE o.customer_id = c.id`), running once per candidate outer row.

Short-Circuit Optimization: The database engine stops scanning the inner table the moment the FIRST matching row is found.

`SELECT 1` Convention: Writing `EXISTS (SELECT 1 FROM ...)` is standard because column projection in the subquery is ignored by the optimizer.

Syntax Blueprint

SELECT c.name
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.id AND o.amount > 500
);

EXISTS evaluates whether the correlated subquery finds at least one matching row.

Core Rules to Remember

Short-Circuit Speed: Terminates inner scan on first match, making it fast on large tables.

Safe with NULLs: `NOT EXISTS` never suffers from the 3VL UNKNOWN null trap that breaks `NOT IN`.

Correlated Execution: Correlates outer candidate rows with inner relational constraints.

Common Beginner Traps & How to Fix Them

Mistake:Using `NOT IN` instead of `NOT EXISTS` when the subquery may contain NULL values.

Why it happens: A single NULL in a `NOT IN` subquery ruins the entire condition and returns 0 rows.

How to fix: Standardize on `NOT EXISTS` for multi-table exclusion checks.

Pro Tip: Use `SELECT 1` or `SELECT *` inside EXISTS; modern query planners completely disregard projected columns inside EXISTS blocks.

Live Interactive Example

Hit Run Code to see it live

Finding Active Customers with EXISTS and Inactive with NOT EXISTS

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: EXISTS identified Alice with orders > $100, and NOT EXISTS isolated Charlie with zero orders.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Filter with EXISTS Subquery

Select `u.name` from `users u WHERE EXISTS (SELECT 1 FROM logs l WHERE l.user_id = u.id)`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.