SQL Subqueries
A query nested inside another query — one of the most powerful tools in SQL.
What is a subquery?
A subquery is a SELECT statement written inside another SQL statement — inside WHERE, FROM, SELECT, or HAVING. The database executes the inner query first, then uses its result in the outer query.
Scalar subquery — returns one value
SQL — orders above the average
SELECT product, amount
FROM orders
WHERE amount > (SELECT AVG(amount) FROM orders);
The subquery returns one number (the average). That number is then used in the WHERE comparison.
List subquery — returns a column of values (IN)
SQL — orders by Indian customers
SELECT *
FROM orders
WHERE customer_id IN (
SELECT id FROM customers WHERE country = 'India'
);
Table subquery — returns a full result set (in FROM)
SQL — top-spending customers
SELECT customer_id, total
FROM (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
) AS spending
WHERE total > 1000
ORDER BY total DESC;
Correlated subquery — references the outer query
SQL — each customer's most recent order
SELECT c.name, o.product, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.id = (
SELECT id FROM orders
WHERE customer_id = c.id -- ← references outer query's c.id
ORDER BY id DESC LIMIT 1
);
EXISTS — check if any rows match
SQL — customers who have placed at least one order
SELECT name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.id
);
EXISTS returns TRUE as soon as it finds the first matching row — it's faster than IN when the subquery result set is large.
CTE (WITH clause) — named subquery
SQL — same query, cleaner with CTE
WITH customer_totals AS (
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
)
SELECT c.name, ct.total
FROM customers c
JOIN customer_totals ct ON c.id = ct.customer_id
WHERE ct.total > 1000
ORDER BY ct.total DESC;
Subquery vs JOIN vs CTE
Use a JOIN when the relationship is clear and you want all columns. Use a subquery for filters (WHERE/HAVING) or when you need an intermediate calculation. Use a CTE when the subquery is complex or used more than once — it's readable and reusable.