Chapter 09 · Subqueries, CTEs, and Set Operations
Correlated Subqueries and EXISTS
A correlated subquery refers to the current row of an outer query. That dependency makes powerful per-row tests possible, but it also changes evaluation and performance reasoning.
Learning outcomes
A correlated subquery contains a reference to an outer query row. The database evaluates the inner logic in the context of each relevant outer row, although the optimizer may transform the physical plan.
Recognize outer references and correlation scope.
Use EXISTS as a duplicate-safe existence test.
Use NOT EXISTS for anti-join questions.
Avoid NULL-sensitive NOT IN errors and support correlated predicates with indexes.
Correlation model
The outer alias behaves like a parameter supplied to the inner query for the current row.
Correlated scalar subquery
SELECT c.customer_id, c.full_name, ( SELECT COUNT(*) FROM sale AS s WHERE s.customer_id = c.customer_id ) AS sale_countFROM customer AS cORDER BY c.customer_id;The reference c.customer_id is resolved in the outer query. Because COUNT always returns one row, the correlated scalar contract is safe.
EXISTS tests whether any row exists
SELECT c.customer_id, c.full_nameFROM customer AS cWHERE EXISTS ( SELECT 1 FROM sale AS s WHERE s.customer_id = c.customer_id AND s.status = 'paid')ORDER BY c.customer_id;The selected constant is conventional; EXISTS cares only whether a row is produced. It can stop logically after the first match and never duplicates the outer customer.
NOT EXISTS is an anti-join
SELECT c.customer_id, c.full_nameFROM customer AS cWHERE NOT EXISTS ( SELECT 1 FROM sale AS s WHERE s.customer_id = c.customer_id)ORDER BY c.customer_id;This expresses absence directly. Marta is retained because no sale row references her customer ID.
Why an ordinary JOIN can be wrong
SELECT c.customer_id, c.full_nameFROM customer AS cJOIN sale AS s ON s.customer_id = c.customer_idWHERE s.status = 'paid'ORDER BY c.customer_id;A customer appears once per matching paid sale. Adding DISTINCT may repair the output, but EXISTS states the requirement more precisely and avoids creating duplicates in the first place.
The NOT IN NULL trap
WITH blocked(value) AS ( VALUES ('Berlin'), (NULL))SELECT 'Tehran' NOT IN (SELECT value FROM blocked) AS not_in_result;The result is NULL, not true. If the subquery can contain NULL, NOT IN may reject every candidate that is not already a positive match.
WITH blocked(value) AS ( VALUES ('Berlin'), (NULL))SELECT 'Tehran' AS candidateWHERE NOT EXISTS ( SELECT 1 FROM blocked AS b WHERE b.value = 'Tehran');Correlated comparison against a group statistic
SELECT s.sale_id, s.customer_id, s.quantityFROM sale AS sWHERE s.quantity > ( SELECT AVG(s2.quantity) FROM sale AS s2 WHERE s2.customer_id = s.customer_id)ORDER BY s.customer_id, s.sale_id;The average is different for each customer. A window function can solve this class of problem later, but the correlated form exposes the dependency clearly.
Index the correlation columns
CREATE INDEX IF NOT EXISTS idx_sale_customer_status ON sale(customer_id, status);EXPLAIN QUERY PLANSELECT c.customer_idFROM customer AS cWHERE EXISTS ( SELECT 1 FROM sale AS s WHERE s.customer_id = c.customer_id AND s.status = 'paid');The useful index begins with the equality columns used inside the correlated predicate. Always confirm the actual plan on the target engine and data distribution.
Practice database
Run this setup once in a disposable SQLite database. Chapter 9 uses customer and sales data for subqueries, an employee hierarchy for recursion, and two lead lists for set operations.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS event_lead;DROP TABLE IF EXISTS web_lead;DROP TABLE IF EXISTS employee;DROP TABLE IF EXISTS sale;DROP TABLE IF EXISTS product;DROP TABLE IF EXISTS customer;CREATE TABLE customer ( customer_id INTEGER PRIMARY KEY, full_name TEXT NOT NULL, city TEXT, segment TEXT NOT NULL CHECK (segment IN ('consumer', 'business')), email TEXT NOT NULL UNIQUE) STRICT;CREATE TABLE product ( product_id INTEGER PRIMARY KEY, product_name TEXT NOT NULL, category TEXT NOT NULL, unit_price REAL NOT NULL CHECK (unit_price >= 0)) STRICT;CREATE TABLE sale ( sale_id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customer(customer_id), product_id INTEGER NOT NULL REFERENCES product(product_id), quantity INTEGER NOT NULL CHECK (quantity > 0), status TEXT NOT NULL CHECK (status IN ('paid', 'pending', 'refunded')), sold_at TEXT NOT NULL) STRICT;CREATE TABLE employee ( employee_id INTEGER PRIMARY KEY, employee_name TEXT NOT NULL, manager_id INTEGER REFERENCES employee(employee_id), job_title TEXT NOT NULL) STRICT;CREATE TABLE web_lead (email TEXT NOT NULL) STRICT;CREATE TABLE event_lead (email TEXT NOT NULL) STRICT;INSERT INTO customer VALUES (1, 'Nadia Rahimi', 'Tehran', 'consumer', 'nadia@example.com'), (2, 'Omar Haddad', 'Berlin', 'business', 'omar@example.com'), (3, 'Lina Chen', NULL, 'consumer', 'lina@example.com'), (4, 'Ava Morgan', 'Berlin', 'consumer', 'ava@example.com'), (5, 'Noah Silva', 'Lisbon', 'business', 'noah@example.com'), (6, 'Marta Costa', NULL, 'business', 'marta@example.com');INSERT INTO product VALUES (10, 'Database Foundations', 'course', 49.00), (11, 'SQL Query Practice', 'course', 69.00), (12, 'SQL Reference Card', 'book', 15.00), (13, 'SQLite Lab Bundle', 'lab', 29.00), (14, 'Data Quality Workbook', 'book', 24.50), (15, 'Database Operations', 'course', 89.00);INSERT INTO sale VALUES (100, 1, 10, 1, 'paid', '2026-08-01 09:15:00'), (101, 2, 11, 3, 'paid', '2026-08-01 10:45:00'), (102, 1, 12, 2, 'paid', '2026-08-02 11:30:00'), (103, 4, 10, 1, 'pending', '2026-08-03 13:05:00'), (104, 2, 13, 2, 'paid', '2026-08-03 15:20:00'), (105, 5, 15, 1, 'paid', '2026-08-04 16:40:00'), (106, 3, 14, 4, 'refunded', '2026-08-05 08:10:00'), (107, 5, 12, 5, 'paid', '2026-08-05 12:00:00'), (108, 2, 15, 1, 'pending', '2026-08-06 09:25:00'), (109, 4, 13, 3, 'paid', '2026-08-06 14:50:00');INSERT INTO employee VALUES (1, 'Amina Yusuf', NULL, 'Chief Data Officer'), (2, 'Jonas Weber', 1, 'Data Engineering Manager'), (3, 'Sara Kim', 1, 'Analytics Manager'), (4, 'Reza Nouri', 2, 'Data Engineer'), (5, 'Elena Rossi', 2, 'Platform Engineer'), (6, 'Maya Patel', 3, 'Analytics Engineer'), (7, 'Leo Martin', 3, 'BI Developer'), (8, 'Daria Novak', 4, 'Junior Data Engineer');INSERT INTO web_lead VALUES ('nadia@example.com'), ('omar@example.com'), ('new-web@example.com'), ('shared@example.com'), ('shared@example.com');INSERT INTO event_lead VALUES ('omar@example.com'), ('lina@example.com'), ('new-event@example.com'), ('shared@example.com'), ('shared@example.com');Each table exposes a different query-composition problem: one-to-many sales, customers without matches, a self-referencing hierarchy, and overlapping lists with duplicates.
Practice lab
- Return customers who bought product 13.
- Return products that have never appeared in a sale.
- Calculate each customer's latest sale timestamp with a correlated scalar subquery.
- Find sales whose quantity exceeds that customer's average.
- Compare EXISTS with a JOIN and count the duplicate rows produced by the join.
SELECT p.product_id, p.product_nameFROM product AS pWHERE NOT EXISTS ( SELECT 1 FROM sale AS s WHERE s.product_id = p.product_id)ORDER BY p.product_id;Checkpoint
Reason about correlation
- Which reference makes a subquery correlated?
- Why does EXISTS not multiply outer rows?
- When is NOT EXISTS safer than NOT IN?
- What does a correlated aggregate calculate?
- Which columns usually belong at the start of a supporting index?
Review the answers
An inner reference to an outer alias creates correlation. EXISTS returns one Boolean result for each outer row. NOT EXISTS is robust when the candidate set may contain NULL. A correlated aggregate is recomputed logically for each outer key. Equality correlation columns usually lead the index.
Summary and references
- Correlation binds inner logic to the current outer row.
- EXISTS and NOT EXISTS express semi-join and anti-join requirements without duplicate multiplication.
- NOT IN becomes unknown when its subquery contains NULL.
- Correlated predicates need indexes and plan verification on realistic data.