Correlated Subquery
In this page:
SELECT columns
FROM outer_table AS o
WHERE column operator (
SELECT aggregate(column) FROM inner_table AS i
WHERE i.column = o.column
);What is a Correlated Subquery?
A correlated subquery is an inner query that reads values from the outer query's current row. It runs once for every row processed by the outer query, unlike a standalone subquery that executes only a single time.
Example: What is a Correlated Subquery?
CREATE TABLE products (id INT, category TEXT, price INT);
INSERT INTO products VALUES (1, 'Books', 10), (2, 'Books', 40), (3, 'Toys', 20);
SELECT * FROM products p1
WHERE price > (SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category);How Correlated Subqueries Work
The database passes a column value from the current row of the outer table to the inner table for each iteration. The inner query evaluates and returns its result using that specific value, then moves to the next outer row.
Example: How Correlated Subqueries Work
CREATE TABLE products (id INT, category TEXT, price INT);
INSERT INTO products VALUES (1, 'Books', 10), (2, 'Books', 40), (3, 'Toys', 20);
SELECT p1.id, p1.category, p1.price,
(SELECT AVG(price) FROM products p2 WHERE p2.category = p1.category) AS category_avg
FROM products p1;Correlated Subquery in WHERE
You will often see correlated subqueries inside the WHERE clause, filtering the outer rows based on a per-row check against related data. This is useful for building dynamic, row-by-row filter rules that a plain join can't always express as cleanly.
Example: Correlated Subquery in WHERE
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT, total INT);
INSERT INTO customers VALUES (1, 'Amit'), (2, 'Priya');
INSERT INTO orders VALUES (101, 1, 500);
SELECT * FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 100);Correlated Subquery in SELECT
You can use correlated subqueries in the SELECT clause to calculate dynamic columns for every returned row, such as showing each customer alongside their most recent order date computed on the fly.
Example: Correlated Subquery in SELECT
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT, order_date TEXT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1, '2024-01-01'), (102, 1, '2024-06-01');
SELECT name,
(SELECT MAX(order_date) FROM orders WHERE orders.customer_id = customers.id) AS latest_order
FROM customers;Performance Considerations
Correlated subqueries can be slow on large tables because of how often they re-execute. Because they run once for every row, they are not always ideal for large datasets. Use JOINS instead if speed becomes an issue, since the optimizer can usually plan those more efficiently.
Example: Performance Considerations
CREATE TABLE customers (id INT, name TEXT);
CREATE TABLE orders (id INT, customer_id INT);
INSERT INTO customers VALUES (1, 'Amit');
INSERT INTO orders VALUES (101, 1);
-- Correlated subquery: re-runs once per outer row
SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- Often faster as a JOIN on large tables
SELECT DISTINCT customers.name FROM customers JOIN orders ON orders.customer_id = customers.id;- Forgetting to link the inner and outer tables, so it is not correlated and returns the same value for every row.
- Using the same table name in both queries without aliases, so the reference is ambiguous.
- Using it on large tables without indexes, when the inner query runs for each outer row.
Chapter Quiz — Complete all 6 topics to unlock
0/6 topics done
Complete these topics first: