SELECT with JOINs

Code

-- 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;

Explanation

PostgreSQL supports standard JOIN types plus LATERAL, which lets a subquery reference columns from the previous FROM item. LATERAL is ideal for top-N-per-group queries without window functions. COALESCE replaces NULL aggregates with a friendly zero, and GROUP BY collapses one-to-many joins.

More PostgreSQL Snippets