Joins & LATERAL

Joins combine rows from two or more tables. PostgreSQL supports all standard join types and adds LATERAL, one of its most powerful and distinctive features. This chapter covers standard joins with practical examples, then dives deep into LATERAL.

Setup — Sample Tables

CREATE TABLE employees (
    id         SERIAL PRIMARY KEY,
    name       TEXT,
    dept_id    INTEGER,
    salary     NUMERIC(10,2),
    manager_id INTEGER
);

CREATE TABLE departments (
    id         SERIAL PRIMARY KEY,
    name       TEXT,
    budget     NUMERIC(12,2)
);

CREATE TABLE projects (
    id          SERIAL PRIMARY KEY,
    title       TEXT,
    dept_id     INTEGER,
    start_date  DATE
);

INSERT INTO departments VALUES (1,'Engineering',500000),(2,'Marketing',200000),(3,'HR',150000);
INSERT INTO employees VALUES
    (1,'Alice',1,95000,NULL),(2,'Bob',1,85000,1),(3,'Carol',2,72000,NULL),
    (4,'Dave',2,68000,3),(5,'Eve',NULL,60000,NULL);
INSERT INTO projects VALUES
    (1,'Alpha',1,'2024-01-01'),(2,'Beta',1,'2024-03-01'),
    (3,'Gamma',2,'2024-02-01'),(4,'Delta',3,'2024-04-01');

INNER JOIN

Returns only rows where there is a match in both tables.

SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

Note: Eve (dept_id IS NULL) is excluded because there's no matching department.

LEFT JOIN (LEFT OUTER JOIN)

Returns all rows from the left table, with NULL for non-matching right-table columns.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

RIGHT JOIN

Returns all rows from the right table, NULLs for non-matching left-table columns.

SELECT e.name, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

HR department appears even though no employee is assigned to it.

FULL JOIN (FULL OUTER JOIN)

Returns all rows from both tables, with NULLs where there is no match.

SELECT e.name, d.name AS department
FROM employees e
FULL JOIN departments d ON e.dept_id = d.id;

CROSS JOIN

Returns the Cartesian product — every row from the first table paired with every row from the second.

-- Generate all size/color combinations
SELECT s.size, c.color
FROM (VALUES ('S'),('M'),('L')) AS s(size)
CROSS JOIN (VALUES ('Red'),('Blue'),('Green')) AS c(color);

Using USING Instead of ON

When the join column has the same name in both tables, USING is cleaner:

-- These are equivalent
SELECT e.name, d.name FROM employees e JOIN departments d ON e.dept_id = d.id;
SELECT e.name, d.name FROM employees e JOIN departments d USING (dept_id);
-- Note: USING produces only one dept_id column in the result (not two)

Self-Join

A table joined to itself — useful for hierarchical relationships:

-- Employee + their manager (both in the same table)
SELECT
    e.name    AS employee,
    m.name    AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.name;

Anti-Join — Find Non-Matching Rows

-- Employees with no projects (using NOT EXISTS)
SELECT e.name
FROM employees e
WHERE NOT EXISTS (
    SELECT 1 FROM projects p WHERE p.dept_id = e.dept_id
);

-- Alternative with LEFT JOIN + IS NULL
SELECT e.name
FROM employees e
LEFT JOIN projects p ON e.dept_id = p.dept_id
WHERE p.id IS NULL;

LATERAL JOIN — The PostgreSQL Star Feature

LATERAL allows a subquery in the FROM clause to reference columns from preceding tables — making it more like a correlated subquery that produces a table, not just a scalar value.

Without LATERAL, a subquery in FROM cannot reference columns from other tables in the same FROM clause. LATERAL removes this restriction.

Syntax

SELECT ...
FROM table1 t1
JOIN LATERAL (
    SELECT ... FROM table2 WHERE table2.col = t1.col  -- can reference t1!
    LIMIT 5
) AS alias ON TRUE;

Example 1: Top N Records Per Group

Get the top 2 earners per department:

-- Without LATERAL (complex with window functions or correlated subqueries)
-- With LATERAL (elegant):

SELECT d.name AS department, top_earners.name, top_earners.salary
FROM departments d
JOIN LATERAL (
    SELECT e.name, e.salary
    FROM employees e
    WHERE e.dept_id = d.id
    ORDER BY e.salary DESC
    LIMIT 2
) AS top_earners ON TRUE
ORDER BY d.name, top_earners.salary DESC;

This is the classic "top N per group" pattern. The subquery runs once per department row, each time using the current d.id to filter employees.

Example 2: LEFT JOIN LATERAL for Optional Results

Use LEFT JOIN LATERAL when you want departments even if no employees match:

SELECT d.name AS department, top_emp.name, top_emp.salary
FROM departments d
LEFT JOIN LATERAL (
    SELECT e.name, e.salary
    FROM employees e
    WHERE e.dept_id = d.id
    ORDER BY e.salary DESC
    LIMIT 1
) AS top_emp ON TRUE;

Example 3: LATERAL with Computed Values

LATERAL can also run functions or complex expressions per row:

-- For each employee, calculate their salary percentile across the company
SELECT
    e.name,
    e.salary,
    pct.percentile
FROM employees e
JOIN LATERAL (
    SELECT
        ROUND(
            100.0 * COUNT(*) FILTER (WHERE e2.salary <= e.salary) / COUNT(*),
            1
        ) AS percentile
    FROM employees e2
) AS pct ON TRUE;

Example 4: LATERAL with JSON/Array Functions

LATERAL is commonly used with UNNEST and JSON functions that produce sets of rows:

-- Unnest an array, keeping the parent row context
SELECT order_id, tag
FROM orders_with_tags,
LATERAL UNNEST(tags) AS tag;
-- Equivalent to: SELECT order_id, UNNEST(tags) FROM orders_with_tags;

-- Parse JSON arrays inline
SELECT user_id, item->>'name' AS item_name
FROM user_carts,
LATERAL jsonb_array_elements(cart_items) AS item;

Join Decision Guide

Performance Tips

-- Always join on indexed columns
-- PostgreSQL can use indexes for JOIN conditions

-- Check your join plan
EXPLAIN ANALYZE
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- The planner picks from: Nested Loop, Hash Join, Merge Join
-- Hash Join is common for larger tables with no useful index
-- Nested Loop is common when one side is small or indexed