Why LATERAL join for correlated subqueries in PostgreSQL? - Purpose & Use Cases

The Big Idea

Discover how a simple join can save you hours of tedious searching and errors!

The Scenario

Imagine you have two lists: one of customers and one of their orders. You want to find the latest order for each customer. Doing this by hand means checking each customer, then searching through all orders to find the newest one. This is like flipping through a huge stack of papers for every single person.

The Problem

Manually comparing each customer to all orders is slow and tiring. It's easy to make mistakes, miss some orders, or get confused by the data. Doing this repeatedly wastes time and can cause errors, especially when the data grows large.

The Solution

The LATERAL join lets the database do this smartly. It runs a small search for each customer to find their latest order, linking the two lists smoothly. This means you get accurate results quickly without messy manual checks.

Before vs After

✗ Before

SELECT customers.id, (SELECT order_date FROM orders WHERE orders.customer_id = customers.id ORDER BY order_date DESC LIMIT 1) FROM customers;

✓ After

SELECT customers.id, latest_orders.order_date FROM customers CROSS JOIN LATERAL (SELECT order_date FROM orders WHERE orders.customer_id = customers.id ORDER BY order_date DESC LIMIT 1) AS latest_orders;

What It Enables

It enables fast, clear queries that find related data for each row without complicated or slow manual searching.

Real Life Example

A store manager wants to see each customer's most recent purchase date to send personalized offers. Using LATERAL join, the manager gets this info instantly for thousands of customers.

Key Takeaways

Manual searching through related data is slow and error-prone.

LATERAL join lets the database efficiently find related rows per item.

This makes queries faster, clearer, and easier to write.