Correlated Subquery

-- Each user with the total of their orders (correlated subquery)
SELECT u.id, u.name,
       (SELECT COALESCE(SUM(amount), 0)
        FROM   orders
        WHERE  user_id = u.id) AS total_spent
FROM   users u;

-- Same query written as a LEFT JOIN + GROUP BY — usually faster
SELECT u.id, u.name, COALESCE(SUM(o.amount), 0) AS total_spent
FROM   users u
LEFT   JOIN orders o ON o.user_id = u.id
GROUP  BY u.id, u.name;

-- Most expensive order per user, via correlated subquery
SELECT u.name,
       (SELECT MAX(amount) FROM orders WHERE user_id = u.id) AS max_order
FROM   users u;

-- Tip: correlated subqueries are fine for ad-hoc reports. For production
-- queries on big tables, rewrite as a JOIN + aggregation if performance matters.