When writing SQL queries in PostgreSQL, we often come across situations where we need a subquery to reference values from a preceding table in the FROM clause. This kind of row-wise dependency can’t be expressed using traditional joins alone, and that's where the LATERAL join comes in.
In this blog, we’ll explore what a LATERAL join is, how it works, and why it's such a powerful feature. We’ll also compare it with alternative approaches when LATERAL isn’t used.
What is a LATERAL Join?
A LATERAL join allows a subquery in the FROM clause to reference columns from the tables that appear before it. Without LATERAL, subqueries are evaluated independently and cannot “see” preceding tables.
Think of LATERAL as a way of making each row in the first table available to the subquery, effectively turning it into a per-row computation.
Example
Let’s imagine a basic schema:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
amount NUMERIC,
order_date TIMESTAMP
);
We want to retrieve each user along with their most recent order.
Using LATERAL
SELECT
u.name,
o.amount,
o.order_date
FROM users u
LEFT JOIN LATERAL (
SELECT amount, order_date
FROM orders
WHERE orders.user_id = u.id
ORDER BY order_date DESC
LIMIT 1
) o ON true;
What’s happening here?
- For each row in users, the subquery finds their latest order.
- The subquery can reference u.id because of the LATERAL keyword.
- ON true is used since the filter is already in the subquery.
What if We Don’t Use LATERAL?
We’d have to resort to workarounds like window functions or DISTINCT ON.
Option 1: DISTINCT ON (PostgreSQL-specific)
SELECT DISTINCT ON (u.id)
u.name, o.amount, o.order_date
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
ORDER BY u.id, o.order_date DESC;
This also fetches the latest order per user, but:
- It depends on DISTINCT ON, which is not standard SQL.
- It can be limiting when the subquery logic is complex.
Option 2: Window Functions
SELECT name, amount, order_date
FROM (
SELECT
u.name,
o.amount,
o.order_date,
ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.order_date DESC) AS rn
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
) sub
WHERE rn = 1;
This works but:
- It can become verbose.
- Performance can degrade if you have additional filtering or aggregates.
Why Use LATERAL?
- Simplifies logic: Especially when doing correlated subqueries.
- Improves readability: Keeps filtering logic close to the data source.
- Enables flexibility: You can use LIMIT, ORDER BY, and aggregates freely.
- Supports more complex operations: Like unnesting arrays or calling set-returning functions row-wise.
Other Use Cases for LATERAL
- Top N per group: Fetch the top N orders for each user.
- Working with JSON: Extract and join JSON keys for each row.
- Dynamic joins: Join with computed or conditional results.
Example: Top 2 orders per user
SELECT u.name, o.amount, o.order_date
FROM users u
JOIN LATERAL (
SELECT *
FROM orders
WHERE orders.user_id = u.id
ORDER BY order_date DESC
LIMIT 2
) o ON true;
PostgreSQL’s LATERAL join is a hidden gem that can transform how you write queries, especially when dealing with per-row logic or correlated subqueries. While there are workarounds using DISTINCT ON or window functions, LATERAL provides a clean, efficient, and expressive solution.
Next time you find yourself trying to fetch a subset of related rows per parent, consider reaching for LATERAL, it might be just the tool you need.