PostgreSQL LEFT JOIN (original) (raw)

Last Updated : 12 Jul, 2025

In **PostgreSQL, the **LEFT JOIN (or **LEFT OUTER JOIN) is a powerful tool that allows you to merge data from two tables based on a related column. With a LEFT JOIN, you get all records from the "left" table and matching records from the "right" table. If there’s no match in the right table, NULL values will fill the corresponding columns.

In this article, we will explain the **PostgreSQL LEFT JOIN in detail, covering its syntax, use cases, and examples to help us understand how to implement it effectively

What is PostgreSQL LEFT JOIN?

A LEFT JOIN in PostgreSQL returns:

All rows from the **first (left) table.
Matching rows from the **second (right) table.
**NULL values for any columns in the **right table that don’t have a corresponding match.

This makes **LEFT JOIN especially useful for **identifying unmatched data between tables, such as records that don’t have related data in another table

**Syntax

SELECT table1.column1, table1.column2, table2.column1, ....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;

**Key Terms

**table1: The primary table from which you want to select data.
**table2: The secondary table that you want to join with the primary table.
**matching_column: The common column between the two tables on which the join condition is based.

Visualizing LEFT JOIN with a Venn Diagram

A Venn diagram can visually illustrate how a LEFT JOIN works. The left circle represents the rows from the **primary table, while the **right circle represents the rows from the **secondary table. The **intersection shows the matching rows, while the left part of the left circle shows rows that have no match in the **secondary table, resulting in **NULL values for those columns.

The below Venn Diagram illustrates the working of **PostgreSQL LEFT JOIN clause:

Venn Diagram of PostgreSQL LEFT JOIN

For understanding of this article we will be using the **sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples.

Examples of PostgreSQL LEFT JOIN

Let us take a look at some of the examples of **LEFT JOIN in **PostgreSQL to better understand the concept. In this database, we have two tables: **film**and inventory. Let’s examine how the LEFT JOIN clause can be applied to these tables.

**Example 1: Basic LEFT JOIN

Here we will use the **LEFT JOIN clause to join the "**film" table to the "**inventory" table. In this example, we’ll retrieve all films from the film table and their corresponding **inventory IDs from the **inventory**table.

**Query:

SELECT
film.film_id,
film.title,
inventory_id
FROM
film
LEFT JOIN inventory ON inventory.film_id = film.film_id;

**Output

**Explanation:

This query returns all films from the "*film" table and their corresponding inventory IDs. If a film has no inventory, the '*inventory_id'**will be **NULL. For instance, "**Movie B" has no inventory, so **inventory_id**is NULL.

**Example 2: Filtering with LEFT JOIN

Here we will use the **LEFT JOIN clause to join the "**film" table to the "**inventory" table and use the **WHERE clause to filter out films that are not in the **inventory supply.

**Query:

SELECT
film.film_id,
film.title,
inventory_id
FROM
film
LEFT JOIN inventory ON inventory.film_id = film.film_id
WHERE
inventory.film_id IS NULL;

**Output

**Explanation:

This query returns all films that are not present in the **inventory. It helps in identifying films that are not available for rent. Since inventory.film_id is **NULL**for "**Movie B," it appears in the output, indicating it’s **not available in inventory.

Important Points About PostgreSQL LEFT JOIN

**Multiple LEFT JOINs: When using multiple **LEFT JOINs in a single query, the order of the joins can affect the result set.
**Using WHERE Clause with LEFT JOIN: While you can use the **WHERE clausefor joining tables (implicit join), it’s recommended to use the explicit **LEFT JOIN syntax for clarity and maintainability.
**LEFT JOIN can be used in self-joins to query hierarchical or recursive relationships within a single table.
**Explicit LEFT JOIN syntax improves query readability, making it easier to understand and maintain, especially in complex queries.

Conclusion

In PostgreSQL, the **LEFT JOIN is an essential type of join that enables the retrieval of all rows from one table, even if there are **no matching entries in the joined table. This makes it particularly valuable when working with **data analysis tasks where we need to highlight unmatched rows or identify missing data across tables. The **PostgreSQL LEFT JOIN is ideal for **reports, **audits, and **detailed data checks, as it offers **flexibility in **querying and improves data visibility.