A correlated subquery uses a column of the outer query, so it is worked out again for each
outer row. EXISTS asks "is there at least one matching row?", and ANY / ALL compare one
value with every value of a list.
A plain subquery gives the same answer for every row, so it only needs to be worked out once. A correlated one is, in principle, worked out again for each outer row.
Databases often rewrite a correlated subquery into a join behind the scenes, so it isn't always slower. The execution plan shows what really happens.
Syntax
Order of execution: for each row of the outer FROM → the subquery runs with that row's
values → WHERE keeps or drops the row → SELECT → ORDER BY
CodeSELECT o.column, (SELECT AGG(i.column) FROM inner_table i WHERE i.key = o.key) AS alias FROM outer_table o WHERE EXISTS (SELECT 1 FROM inner_table i WHERE i.key = o.key) AND o.value > ALL (SELECT value FROM other_table WHERE condition);
| Test | True when |
|---|---|
EXISTS (…) | the subquery returns at least one row |
NOT EXISTS (…) | it returns no rows |
x > ANY (…) | x is greater than at least one value (= ANY is the same as IN) |
x > ALL (…) | x is greater than every value (also true when the list is empty) |
Examples
A subquery per row
Each order is compared with the average of its own customer's orders. The subquery can't run on
its own, because it needs o from the outer query.
CodeSELECT o.order_id, o.total_amount FROM orders o WHERE o.customer_id = 3 AND o.total_amount > (SELECT AVG(o2.total_amount) FROM orders o2 WHERE o2.customer_id = o.customer_id) ORDER BY o.order_id; -- Fails: run alone, the inner query doesn't know o SELECT AVG(o2.total_amount) FROM orders o2 WHERE o2.customer_id = o.customer_id;
A count per row in SELECT
CodeSELECT p.product_name, (SELECT COUNT(*) FROM reviews r WHERE r.product_id = p.product_id) AS reviews FROM products p WHERE p.category_id = 2 ORDER BY p.product_id;
EXISTS and NOT EXISTS
EXISTS only checks whether a row exists, so SELECT 1 is enough.
CodeSELECT c.full_name FROM customers c WHERE EXISTS (SELECT 1 FROM payments p WHERE p.customer_id = c.customer_id AND p.method = 'wallet') ORDER BY c.full_name; SELECT p.product_id, p.product_name FROM products p WHERE NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.product_id = p.product_id);
EXISTS as a true/false column
PostgreSQL and MySQL can show a condition as a column. SQL Server and older Oracle need
CASE WHEN EXISTS (…) THEN … END.
CodeSELECT c.full_name, EXISTS (SELECT 1 FROM accounts a WHERE a.customer_id = c.customer_id) AS has_wallet FROM customers c WHERE c.city = 'Kochi' ORDER BY c.full_name;
ANY and ALL
Compare one value with a whole list.
Code-- Pricier than every Toys product SELECT product_name, price FROM products WHERE price > ALL (SELECT price FROM products WHERE category_id = 8) ORDER BY price; -- Pricier than at least one Toys product SELECT product_name, price FROM products WHERE price > ANY (SELECT price FROM products WHERE category_id = 8) ORDER BY price;
Copy only the rows not copied yet
NOT EXISTS in an INSERT … SELECT skips rows that are already in the target table, so running
the statement twice adds nothing new.
CodeCREATE TABLE wallet_owners (customer_id INT PRIMARY KEY, full_name VARCHAR(100)); -- Copy the customers who have a wallet INSERT INTO wallet_owners (customer_id, full_name) SELECT c.customer_id, c.full_name FROM customers c WHERE EXISTS (SELECT 1 FROM accounts a WHERE a.customer_id = c.customer_id) AND NOT EXISTS (SELECT 1 FROM wallet_owners w WHERE w.customer_id = c.customer_id); -- The same statement again: everyone is already there, so it adds no rows and breaks no key INSERT INTO wallet_owners (customer_id, full_name) SELECT c.customer_id, c.full_name FROM customers c WHERE EXISTS (SELECT 1 FROM accounts a WHERE a.customer_id = c.customer_id) AND NOT EXISTS (SELECT 1 FROM wallet_owners w WHERE w.customer_id = c.customer_id);
Delete copies, keep the first
A row is a copy when an earlier row, one with a smaller id, has the same values. DELETE … WHERE EXISTS with that condition removes every copy and keeps the first row of each.
CodeCREATE TABLE city_signups (signup_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, city VARCHAR(50)); INSERT INTO city_signups (city) SELECT city FROM customers; -- many cities repeat DELETE FROM city_signups s WHERE EXISTS (SELECT 1 FROM city_signups e WHERE e.city = s.city AND e.signup_id < s.signup_id); SELECT city, signup_id FROM city_signups ORDER BY city; -- each city once
A new value per row in UPDATE
A correlated subquery in SET works out a different value for each row it updates.
Code-- Set each reviewed product's rating to the average of its reviews UPDATE products p SET rating = (SELECT ROUND(AVG(r.rating), 1) FROM reviews r WHERE r.product_id = p.product_id) WHERE EXISTS (SELECT 1 FROM reviews r WHERE r.product_id = p.product_id);
Remember
- Link the subquery to the outer row through the outer alias, such as
i.key = o.key. - Without that link, the subquery gives the same answer for every row.
- A correlated subquery can't run on its own, because it needs the outer row.
NOT EXISTSworks with NULLs;NOT INreturns nothing if the list has a NULL.> ALLmust beat the largest value, while> ANYonly has to beat the smallest.- To delete copies, compare every column that makes two rows the same, and keep the smaller id.
Practice
- Categories Without Their Own Products (Medium)
- Between Books and Electronics (Medium)
- Customers Who Never Cancelled (Medium)
- Remove Orders Saved Twice (Easy)