Postgres - Lateral Join

2022-06-04

Today I have another Postgres feature to eschew wonder and adulation for. The lateral Join.

While other databases also support lateral joins; the past half a decade I have been using mostly postgres. It's just what people have been paying me for.

Select Highest recent sales


SELECT customers.name, customers.contact_info, top_sales.*
FROM customers
LEFT JOIN LATERAL (
    SELECT customer_id, amount, category, created_at
    FROM sales
    WHERE customer_id = customers.id
    ORDER BY amount DESC
    LIMIT 5
) AS top_sales ON true

What does it do?

In-short, this is a way to get a report of all customers, with their greatest purchases. I've included a created_at column which can be used inside the inner-query, or just for display purposes. This sort of insight can answer important questions you may have about a customer; and can be used with other types of data to allow getting a limited subset of joined data.

I Hope you found this useful.

By Lewis Cowles