Subqueries
Nest queries within queries
A subquery is a query nested inside another query. They're useful for complex filtering and when you need to use one query's result in another.
Subquery in WHERE
-- Find users who have placed orders
SELECT * FROM users
WHERE id IN (
SELECT DISTINCT user_id FROM orders
);
-- Products priced above average
SELECT * FROM products
WHERE price > (
SELECT AVG(price) FROM products
);
-- Users who spent more than $500 total
SELECT * FROM users
WHERE id IN (
SELECT user_id FROM orders
GROUP BY user_id
HAVING SUM(total) > 500
);Subquery in FROM (Derived Table)
-- Use aggregated results as a table
SELECT
u.name,
stats.order_count,
stats.total_spent
FROM users u
JOIN (
SELECT
user_id,
COUNT(*) AS order_count,
SUM(total) AS total_spent
FROM orders
GROUP BY user_id
) AS stats ON u.id = stats.user_id;Correlated Subqueries
References the outer query, runs once per row:
-- Each user's most recent order
SELECT * FROM orders o1
WHERE created_at = (
SELECT MAX(created_at)
FROM orders o2
WHERE o2.user_id = o1.user_id
);
-- Products priced above their category average
SELECT * FROM products p1
WHERE price > (
SELECT AVG(price)
FROM products p2
WHERE p2.category_id = p1.category_id
);EXISTS vs IN
-- EXISTS stops at first match (often faster)
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- NOT EXISTS for exclusion
SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);Tip: EXISTS is often faster than IN for large datasets because it stops searching as soon as it finds a match.