SQL subqueries
A subquery is a query in parentheses used inside another query. It lets you ask “which items cost more than average?” without hard-coding the average.
A single value
A subquery that returns exactly one value (one row, one column) can be used anywhere a value can, for example with >.
SELECT name, price FROM menu_items WHERE price > (SELECT AVG(price) FROM menu_items);
A list with IN
A subquery that returns one column can feed IN.
SELECT name FROM customers WHERE id IN (SELECT customer_id FROM orders WHERE status = 'cancelled');
EXISTS and NOT EXISTS
EXISTS asks whether the subquery returns any row at all. NOT EXISTS is the safe way to find things with no match, because unlike NOT IN it isn't tripped up by NULLs.
SELECT c.name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id );
Subqueries in FROM
A subquery in FROM (a derived table) acts like a temporary table. It needs an alias. For anything long, a CTE (WITH) reads better.
SELECT AVG(total) AS avg_order FROM ( SELECT oi.order_id, SUM(oi.quantity * m.price) AS total FROM order_items oi JOIN menu_items m ON m.id = oi.item_id GROUP BY oi.order_id ) totals;
Common mistakes
Using
=with a subquery that returns several rows. Use IN, or aggregate to a single value.NOT IN (subquery)when the subquery can return NULL: the result is empty. Use NOT EXISTS.Correlated subqueries over large tables. A JOIN with GROUP BY is often much faster.
Questions
Subquery or JOIN?+
Use whichever says what you mean. Modern databases often plan them the same way. EXISTS is great for “has at least one”, JOIN when you need columns from both tables.
Subquery or CTE?+
Same power. CTEs (WITH) name each step and read top to bottom, which helps once queries get long.
Reading is step one. Now make it stick.
Practise this in a five-minute lesson with instant feedback. No account needed.