Joins in PostgreSQL

Joining Tables

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Join Types

• INNER JOIN: only matching rows on both sides.
• LEFT JOIN: all rows from the left table plus matches.
• RIGHT JOIN: all rows from the right table plus matches.
• FULL OUTER JOIN: all rows from both, missing pairs as NULL.
• CROSS JOIN: every combination of rows.

Lateral Joins (Advanced)

LATERAL lets the right side reference columns of the left side, enabling per-row subqueries.

SELECT c.name, o.total
FROM customers c
CROSS JOIN LATERAL (
  SELECT total FROM orders WHERE customer_id = c.id ORDER BY total DESC LIMIT 1
) o;

Aggregating After a Join

SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total) AS lifetime
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
HAVING COUNT(o.id) >= 1
ORDER BY lifetime DESC;

Key Points

• JOIN ... ON pairs rows using a condition.
• LEFT JOIN keeps the left side whole; NULLs fill the gap.
• LATERAL enables powerful per-row subqueries.
• Combine joins with GROUP BY for summaries.