Understanding PostgreSQL LATERAL Joins with Practical Examples

May 23, 2025

PostgreSQL has a powerful feature called Lateral joins, which lets you reference columns from earlier tables in the FROM clause. This becomes especially useful when working with data that changes over time, like historical addresses or versioned records.

Architecture Joins

For example, consider a domain where we keep track of customers and statements. Customers can change their addresses over time, but it’s important that a statement has the appropriate historical address for a customer.

CREATE TABLE addresses (
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  address TEXT NOT NULL,
  effective_on DATE NOT NULL,
  PRIMARY KEY (customer_id, effective_on)
);

CREATE TABLE statements (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  statement_date DATE NOT NULL,
  balance NUMERIC(10, 2) NOT NULL
);

I have created some sample data:

SELECT
  customers.id
, customers.name
, addresses.address
, addresses.effective_on
FROM
  customers
INNER JOIN
  addresses
ON
  customers.id = addresses.customer_id
;

 id | name  |   address   | effective_on
----+-------+-------------+--------------
  1 | Alice | 123 Main St | 2024-01-01
  1 | Alice | 456 Oak Ave | 2024-04-01
  2 | Bob   | 789 Pine Rd | 2024-02-15

Think of the Lateral join as a kind of “for-each loop” inside SQL: for each row in statements, it runs a subquery on addresses using the statement_date. Without LATERAL, this would require a more verbose join with DISTINCT ON, a window function, or a nested subquery.

SELECT
  statements.id
, statements.statement_date
, customers.name
, statement_addresses.address
FROM
  statements
JOIN LATERAL (
  SELECT
    addresses.address
  FROM
    addresses
  WHERE
    addresses.customer_id = statements.customer_id
  AND
    addresses.effective_on <= statements.statement_date
  ORDER BY
    addresses.effective_on DESC
  LIMIT 1
 ) statement_addresses
ON
  TRUE
JOIN
  customers
ON
  customers.id = statements.customer_id
;

 id | statement_date | name  |   address
----+----------------+-------+-------------
  1 | 2024-02-01     | Alice | 123 Main St
  2 | 2024-05-01     | Alice | 456 Oak Ave
  3 | 2024-03-01     | Bob   | 789 Pine Rd

Over the past few years, I’ve become more of an advocate for tracking changes over time and using SQL to reconstruct the state of data at any given point. PostgreSQL’s LATERAL joins make this approach even more powerful, enabling elegant, performant queries that access related, time-sensitive records with minimal fuss.

PostgreSQL