PostgreSQL Left Join

Summary: in this tutorial, you will learn how to use the PostgreSQL LEFT JOIN clause to merge rows of two tables.

Introduction to PostgreSQL LEFT JOIN clause

The LEFT JOIN is an optional clause of the SELECT statement. The LEFT JOIN clause merges rows from two tables and returns all rows from the left table and matching rows from the right table.

Here’s the syntax of the LEFT JOIN clause:

SELECT
  left_table.column1,
  right_table.column2,
  ...
FROM
  left_table
  LEFT JOIN right_table ON right_table.column1 = left_table.column1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, specify the name of the left table (left_table) in the FROM clause:

FROM left_tableCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Second, provide the name of the right table (right_table) you want to join in the LEFT JOIN clause:

LEFT JOIN right_tableCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Third, use a condition to match the rows in the left table (left_table) with the rows in the right table (right_table) in the ON clause:

ON left_table.column1 = right_table.column1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This condition matches rows by comparing the values of column1 in the left table (left_table) with the values of column1 in the right table (right_table):

right_table.column1 = left_table.column1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL always includes the row from the left table with the matching row from the right table.

If a row from the left table does not have a matching row in the right table, PostgreSQL does the following:

• First, creates a “fake” row with columns from the right table.
• Second, fill all the columns with NULL.
• Third, merge the “fake” row with the row from the left table.

Finally, return the columns specified in the SELECT clause:

SELECT
  left_table.column1,
  right_table.column2,
  ...Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that the table you specify in the FROM clause is the left table, while the table you provide in the LEFT JOIN clause is the right table.

PostgreSQL LEFT JOIN clause example

Let’s take an example of using the LEFT JOIN clause.

Setting up sample tables

Suppose we have two tables brands and products:

CREATE TABLE brands (
  brand_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL
);

CREATE TABLE products (
  product_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  brand_id INT NOT NULL,
  FOREIGN KEY (brand_id) REFERENCES brands (brand_id)
);

INSERT INTO
  brands (name)
VALUES
  ('Apple'),
  ('Samsung'),
  ('Google') 
RETURNING *;

INSERT INTO
  products (name, price, brand_id)
VALUES
  ('iPhone 14 Pro', 999.99, 1),
  ('iPhone 15 Pro', 1199.99, 1),
  ('Galaxy S23 Ultra', 1149.47, 2) 
RETURNING *;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The products table has a foreign key column, brand_id, that references the brand_id primary key column of the brands table.

The brands table:

The products table:

Joining tables using the LEFT JOIN clause

The following statement uses the LEFT JOIN clause to select all rows from the brands table with the matching rows from the products table:

SELECT
  brands.name,
  products.name,
  products.price
FROM
  brands
  LEFT JOIN products ON products.brand_id = brands.brand_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

  name   |       name       |  price
---------+------------------+---------
 Apple   | iPhone 14 Pro    |  999.99
 Apple   | iPhone 15 Pro    | 1199.99
 Samsung | Galaxy S23 Ultra | 1149.47
 Google  | NULL             |    NULLCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

How it works.

First, the FROM clause examines each row in the brands table.

Second, the LEFT JOIN clause compares values in the brand_id column of each row in the brands table with values in the brand_id column of each row in the products table.

If they are equal, PostgreSQL merges the rows from both tables. If they are not equal, PostgreSQL creates a new row with the columns from the left table and columns from the right table filled with NULL.

The brand with id 1 matches two rows in the products table:

The brand with id 2 matches one row in the products table:

The brand with id 3 does not match any row in the products table. PostgreSQL creates a fake rows for all columns corresponding to columns in the products table, fills them will NULL and merge with the row in the left table:

After examining all rows in the brands and products table, PostgreSQL comes up with the following intermediate result:

Third, return the columns specified in the SELECT statement:

The output has two name columns, one from the brands table and another comes from the products table.

You can use column aliases to make them more clear:

SELECT
  brands.name brand_name,
  products.name product_name,
  products.price
FROM
  brands
  LEFT JOIN products ON products.brand_id = brands.brand_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 brand_name |   product_name   |  price
------------+------------------+---------
 Apple      | iPhone 14 Pro    |  999.99
 Apple      | iPhone 15 Pro    | 1199.99
 Samsung    | Galaxy S23 Ultra | 1149.47
 Google     | NULL             |    NULLCode language: PHP (php)

Left joining tables with table aliases

A table alias is a temporary name that you assign to a table during the query execution. The following statement uses table aliases for the brands and products tables:

SELECT
  b.name AS brand_name,
  p.name AS product_name,
  p.price
FROM
  brands b
  LEFT JOIN products p ON p.brand_id = b.brand_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL LEFT JOIN clause with the USING syntax

When joining two tables by comparing values from the same column names using the equal operator (=), you can use the USING clause syntax:

SELECT
  left_table.column1,
  right_table.column2
FROM
  left_table
  LEFT JOIN right_table USING (column1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

We use the USING clause instead of the ON clause in the LEFT JOIN in this statement.

The following example joins the brands table with the products table using the LEFT JOIN clause with the USING syntax:

SELECT
  p.name product_name,
  p.price,
  b.name brand_name
FROM
  products p
  LEFT JOIN brands b USING (brand_id);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary

• Use the LEFT JOIN clause to merge rows from two tables and return all rows from the left table with the matching rows from the right table.
• Use the USING syntax when left joining two tables using the same column name and equal operator.