Subqueries nest a SELECT inside another query. Use them with IN, EXISTS, or as a scalar value. They solve complex retrieval problems but CTEs are often more readable for multi-step queries.
Subqueries
-- Users who placed an order
SELECT name FROM users
WHERE id IN (SELECT DISTINCT user_id FROM orders);
-- Users with above-average orders
SELECT name FROM users u
WHERE (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) > 3;
-- Scalar subquery
SELECT name,
(SELECT COUNT(*) FROM orders WHERE user_id = u.id) AS order_count
FROM users u;