SELECT with JOINs

-- INNER JOIN with alias
SELECT c.name, o.id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'completed';

-- LEFT JOIN with COALESCE for NULLs
SELECT c.name, COALESCE(SUM(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id;

-- LATERAL join: subquery per row
SELECT c.name, latest.id AS last_order
FROM customers c
LEFT JOIN LATERAL (
  SELECT id FROM orders o
  WHERE o.customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 1
) latest ON true;