Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-left-join/ below:

PostgreSQL - LEFT JOIN - GeeksforGeeks

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

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.

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:

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.

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.

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.

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.

• 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 clause for 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.

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.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4