Introduction to PostgreSQL LEFT JOIN clause

TheLEFT JOIN clausejoins a left table with the right table and returns the rows from the left table that may or may not have corresponding rows in the right table.

TheLEFT JOIN can be useful for selecting rows from one table that do not have matching rows in another.

Here’s the basic syntax of theLEFT JOIN clause:

SELECT  select_listFROM  table1LEFT JOIN table2  ON table1.column_name = table2.column_name;

In this syntax:

• First, specify the columns from both tables in the select list (select_list) of the SELECT clause.
• Second, specify the left table (table1) from which you want to select data in the FROM clause.
• Third, specify the right table (table2) you want to join using the LEFT JOIN keyword.
• Finally, define a condition for the join (table1.column_name = table2.column_name), which indicates the column (column_name) in each table should have matching values.

How the LEFT JOIN works

TheLEFT JOIN clause starts selecting data from the left table (table1). For each row in the left table, it compares the value in thecolumn_name with the value of the corresponding column from every row in the right table.

When these values are equal, the left join clause generates a new row including the columns that appear in theselect_list and appends it to the result set.

If these values are not equal, theLEFT JOIN clause creates a new row that includes the columns specified in theSELECT clause. Additionally, it populates the columns that come from the right table with NULL.

Note that LEFT JOIN is also referred to as LEFT OUTER JOIN.

If the columns for joining two tables have the same name, you can use theUSING syntax:

SELECT  select_listFROM  table1  LEFT JOIN table2USING (column_name);

The following Venn diagram illustrates how theLEFT JOIN clause works:

PostgreSQL LEFT JOIN examples

Let’s look at the followingfilm andinventory tables from thesample database.

Each row in thefilm table may correspond to zero or multiple rows in theinventory table.

Conversely, each row in theinventory table has one and only one row in thefilm table.

The linkage between thefilm andinventory tables is established through thefilm_id column.

1) Basic PostgreSQL LEFT JOIN examples

The following statement uses theLEFT JOIN clause to joinfilm table with theinventory table:

SELECT  film.film_id,  film.title,  inventory.inventory_idFROM  film  LEFT JOIN inventoryON inventory.film_id= film.film_idORDER BY  film.title;

When a row from thefilm table does not have a matching row in theinventory table, the value of theinventory_id column of this row isNULL.

The following statement uses table aliases andLEFT JOIN clause to join thefilm andinventory tables:

SELECT  f.film_id,  f.title,  i.inventory_idFROM  film f  LEFT JOIN inventory iON i.film_id= f.film_idORDER BY  i.inventory_id;

Because thefilm andinventory tables share the samefilm_id column, you can use theUSING syntax:

SELECT  f.film_id,  f.title,  i.inventory_idFROM  film f  LEFT JOIN inventory iUSING (film_id)ORDER BY  i.inventory_id;

2) Using PostgreSQL LEFT JOIN with WHERE clause

The following uses theLEFT JOIN clause to join theinventory andfilm tables. It includes aWHERE clause that identifies the films that are not present in the inventory:

SELECT  f.film_id,  f.title,  i.inventory_idFROM  film f  LEFT JOIN inventory iUSING (film_id)WHERE  i.film_idIS NULLORDER BY  f.title;

Output:

film_id |         title          | inventory_id---------+------------------------+--------------      14 | Alice Fantasia         |         null      33 | Apollo Teen            |         null      36 | Argonauts Town         |         null      38 | Ark Ridgemont          |         null      41 | Arsenic Independence   |         null...

Summary

• Use the PostgreSQLLEFT JOIN clause to select rows from one table that may or may not have corresponding rows in other tables.

Last updated onFebruary 7, 2024

On this page

• Introduction to PostgreSQL LEFT JOIN clause
• PostgreSQL LEFT JOIN examples
• Summary