PostgreSQL LATERAL JOIN

Introduction

When working with SQL, we often join tables to retrieve related data. However, standard joins can be limiting, especially when you dynamically filter or limit the number of associated rows per record. This is where PostgreSQL's LATERAL JOIN comes in.

LATERAL JOIN allows you to reference columns from the left table inside the right-side subquery, executing it for each row of the left table. This makes it incredibly useful for scenarios like fetching the three most recent purchases for each customer.

Let's break this down with a practical example.

Understanding LATERAL JOIN with an Example

Query Breakdown

SELECT customers.*, recent_sales.*
FROM customers
LEFT JOIN LATERAL (
    SELECT *
    FROM sales
    WHERE sales.customer_id = customers.id
    ORDER BY created_at DESC
    LIMIT 3
) AS recent_sales ON true;
        

Step-by-step Explanation:

1. Selecting from the customers' table → We retrieve all customer details.
2. LATERAL JOIN with sales table → We want only the last 3 sales for each customer.
3. Subquery inside LATERAL JOIN: Filters sales matching the customer_id. Orders sales by created_at DESC (latest first). Limits the result to 3 per customer.
4. Why ON true? LEFT JOIN LATERAL requires a condition, but since we apply filters inside the subquery, we use ON true to include all customers, even those with no sales.

Creating a Sample Database

Step 1: Create Tables

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name TEXT
);

CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id),
    product TEXT,
    created_at TIMESTAMP DEFAULT now()
);
        

Step 2: Insert Sample Data

INSERT IN CUSTOMERS

INSERT INTO customers (name) VALUES
('Alice'),
('Bob'),
('Charlie');        

SELECT * FROM CUSTOMERS

INSERT IN SALES

INSERT INTO sales (customer_id, product, created_at) VALUES
(1, 'Laptop', '2024-03-18 10:00:00'),
(1, 'Phone', '2024-03-19 12:00:00'),
(1, 'Tablet', '2024-03-20 14:00:00'),
(1, 'Headphones', '2024-03-21 16:00:00'),  -- Should not appear in result (4th purchase)

(2, 'Keyboard', '2024-03-18 11:00:00'),
(2, 'Mouse', '2024-03-19 13:00:00'),

(3, 'Monitor', '2024-03-20 15:00:00'),
(3, 'Desk', '2024-03-21 17:00:00'),
(3, 'Chair', '2024-03-22 18:00:00'),
(3, 'Lamp', '2024-03-23 19:00:00');  -- Should not appear in result (4th purchase)        

SELECT * FROM SALES

Step 3: Execute the Query

SELECT customers.*, recent_sales.*
FROM customers
LEFT JOIN LATERAL (
    SELECT *
    FROM sales
    WHERE sales.customer_id = customers.id
    ORDER BY created_at DESC
    LIMIT 3
) AS recent_sales ON true;
        

Expected Result:

Why Use LATERAL JOIN?

1. Efficient Filtering → Unlike a standard JOIN, it allows filtering before joining, reducing unnecessary rows.
2. Dynamic Queries → Works like a for-each loop inside SQL, handling row-by-row computations efficiently.
3. Better Than GROUP BY → Unlike GROUP BY ARRAY_AGG, which returns JSON-like results, LATERAL JOIN keeps it in tabular format.
4. Preserves Customer Data → Ensures that even customers with no purchases are included (via LEFT JOIN).

Final Thoughts

PostgreSQL's LATERAL JOIN is a powerful tool that simplifies complex queries where you need to apply row-level filtering. By understanding its behavior, you can optimize your database queries for better performance and readability.

If you found this guide helpful, share your thoughts in the comments or connect with me for more database insights! 🚀