Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

AdvancedJOINs (INNER, LEFT, RIGHT, FULL)

LATERAL JOIN: employee with last salary change

The query

SQL
SELECT
  e.first_name,
  e.last_name,
  e.salary AS current_salary,
  lsh.old_salary,
  lsh.new_salary,
  lsh.changed_at,
  lsh.reason
FROM employees e
LEFT JOIN LATERAL (
  SELECT *
  FROM salary_history sh
  WHERE sh.emp_id = e.emp_id
  ORDER BY sh.changed_at DESC
  LIMIT 1
) lsh ON TRUE;
Tested against PostgreSQL 16

Note

“LATERAL JOIN is PostgreSQL's correlated subquery in FROM. Extremely powerful for per-row operations.

Tables referenced