Why LATERAL subqueries in PostgreSQL? - Purpose & Use Cases

The Big Idea

Discover how to fetch related data for each row effortlessly with one simple trick!

The Scenario

Imagine you have a list of customers and for each customer, you want to find their latest order details. Doing this manually means checking each customer one by one and then searching through all orders to find the latest one.

The Problem

This manual approach is slow and tiring because you repeat the same search many times. It's easy to make mistakes, like mixing up orders or missing the latest one. Also, writing separate queries for each customer is confusing and hard to manage.

The Solution

LATERAL subqueries let you write one smart query that looks at each customer and immediately finds their latest order. It works like a helper that can use information from the current customer row to find matching orders quickly and cleanly.

Before vs After

✗ Before

SELECT customer_id FROM customers;
-- Then for each customer, run:
SELECT * FROM orders WHERE customer_id = ? ORDER BY order_date DESC LIMIT 1;

✓ After

SELECT c.customer_id, o.*
FROM customers c,
LATERAL (
  SELECT * FROM orders o WHERE o.customer_id = c.customer_id ORDER BY order_date DESC LIMIT 1
) o;

What It Enables

It enables writing clear and efficient queries that combine related data row-by-row without complex joins or repeated queries.

Real Life Example

A store manager wants to see each customer's most recent purchase to offer personalized discounts. Using LATERAL subqueries, they get this info instantly for all customers in one go.

Key Takeaways

LATERAL subqueries let you use data from one row to find related rows easily.

They simplify queries that need to look up matching details for each item in a list.

This makes your database work faster and your queries easier to read.