Subqueries: Scalar, IN, EXISTS and Correlated
A query inside a query - answer one question to help answer a bigger one
Big Idea: A subquery is a complete
SELECT written inside another statement. The outer query uses whatever the inner query returns - a single value, a list of values, or just a yes/no answer to "does any matching row exist?". Mastering the four shapes (scalar, IN, EXISTS and correlated) lets you express questions that a single flat query cannot.
By the end you will be able to:
- write scalar subqueries in
WHEREandSELECT, - use a subquery in
FROMas a derived table, - filter with
INand the crucialNOT IN+ NULL trap, - test for existence with
EXISTS/NOT EXISTS, - recognise a correlated subquery and reason about its cost,
- rewrite many subqueries as joins and know when to.
1. Our example shop database
Every query below runs against one small online-shop schema. Read the three tables once now; we will reuse them for the whole topic.
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(50),
city VARCHAR(50)
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
name VARCHAR(50),
price DECIMAL(8,2)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
product_id INT,
quantity INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
INSERT INTO customers VALUES
(1,'Alice','London'), (2,'Bob','Paris'),
(3,'Carol','London'), (4,'Dan','Berlin');
INSERT INTO products VALUES
(1,'Keyboard',45.00), (2,'Mouse',25.00),
(3,'Monitor',220.00), (4,'Webcam',60.00);
INSERT INTO orders VALUES
(1,1,3,1,'2026-01-05'), (2,1,2,2,'2026-01-06'),
(3,2,1,1,'2026-02-10'), (4,3,3,1,'2026-02-11'),
(5,3,4,1,'2026-03-01');Note two facts that make the examples interesting: Dan (customer 4) has placed no orders, and the Monitor (product 3) is by far the most expensive item. Here is the raw orders table:
| order_id | customer_id | product_id | quantity | order_date |
|---|---|---|---|---|
| 1 | 1 | 3 | 1 | 2026-01-05 |
| 2 | 1 | 2 | 2 | 2026-01-06 |
| 3 | 2 | 1 | 1 | 2026-02-10 |
| 4 | 3 | 3 | 1 | 2026-02-11 |
| 5 | 3 | 4 | 1 | 2026-03-01 |