Subqueries
🏠The everyday picture
"Show me customers who spent more than the average customer." First you need to know the average; then you compare each customer with it. One question inside another.
📘What it really is
- A subquery is a query inside another query, in brackets:
-- Orders larger than the average order
SELECT id, total_paise
FROM orders
WHERE total_paise > (SELECT AVG(total_paise) FROM orders);
- In
WHERE ... IN:
-- Customers who bought product 3
SELECT name FROM customers
WHERE id IN (SELECT o.customer_id
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE oi.product_id = 3);
EXISTSchecks whether any matching row exists (often clearer and faster for yes/no questions):
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
- A subquery in
FROMacts as a temporary table, which you can then filter or join. - Many subqueries can also be written as joins. Choose whichever is clearer to read.
🖱️Try it
Write: orders above the average order value; customers who bought a specific product; customers who ordered in September
but not in October (using EXISTS and NOT EXISTS); and products whose price is above the average price in their
category.
✅Quick check
When is EXISTS a good choice?
Show the answer
For yes/no questions like "does this customer have any orders?"
Can a subquery's result be used like a table?
Show the answer
Yes. A subquery in FROM acts as a temporary table that can be filtered or joined.