Postgres LATERAL Join

In this tutorial, we will explore the LATERAL joins in PostgreSQL. We will cover the LATERAL joins, how they work, and how we can use them. We will also provide the detailed and valuable examples of LATERAL joins using the Pagila database.

Prerequisites:

Install PostgreSQL – If you haven’t already installed PostgreSQL, you can download it from the official website and follow the installation instructions for your operating system.

Setup the Pagila database – Download the Pagila database from the official repository and import it into your PostgreSQL server.

With the prerequisites out, let’s move on to understanding the LATERAL JOIN in PostgreSQL.

Introduction to LATERAL JOIN

LATERAL join is an incredible feature which is introduced in PostgreSQL 9.3 and above. This feature allows us to reference the columns from previous tables in a join and use them as input for the subsequent tables.

LATERAL joins are especially useful to correlate the data between tables where the result of one table can influence the join condition or columns of another table.

Syntax:
The following expresses the syntax of a LATERAL join in PostgreSQL:

SELECT column_list
FROM table1
LATERAL JOIN table2 ON join_condition;

PostgreSQL LATERAL Join Examples

Let us explore the practical examples of working with a LATERAL join using the Pagila database.

Example:
Take the Pagila database. Suppose we wish to retrieve a list of customers along with the details of their latest rental. We can achieve this using a LATERAL join as demonstrated in the following query:

SELECT c.customer_id, c.first_name, c.last_name, r.rental_id, r.rental_date
FROM customer c
LEFT JOIN LATERAL (
SELECT rental_id, rental_date
FROM rental
WHERE customer_id = c.customer_id
ORDER BY rental_date DESC
LIMIT 1
) r ON true;

In this query, we use the LATERAL JOIN syntax to join the result of the subquery with the customer table.

The subquery selects the latest rental for each customer by filtering on the customer_id, ordering the rentals by date in descending order, and limiting the result to only one row per customer using LIMIT 1.

The LEFT JOIN with ON true ensures that all rows from the customer table are included in the result, even if there are no corresponding rentals.

Conclusion

This fundamental tutorial covers the basics of working with LATERAL joins in PostgreSQL and how to use them to perform joins in the Pagila database.

Postgres LATERAL Join

Prerequisites:

Introduction to LATERAL JOIN

PostgreSQL LATERAL Join Examples

Conclusion

About the author

John Otieno