PostgreSQL Right Join

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

Introduction to PostgreSQL RIGHT JOIN clause

The RIGHT JOIN clause allows you to merge rows from two tables, return all rows from the right table, and matching rows from the left table.

Here’s the syntax of the RIGHT JOIN clause:

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

In this syntax:

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

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

Second, specify the name of the right table (right_table) you want to join with the left table in the RIGHT JOIN clause:

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

Third, use a condition for matching rows from the right table (left_table) with rows from the left table (left_table) in the ON clause:

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

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

PostgreSQL always includes the row from the right table with the matching row from the left table. Matching means both rows from tables have the same value in the column1 columns.

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

• Create a “fake” row with columns from the left table.
• Fill all the columns with NULL
• Merge with the “fake” row with the row from the right table.

Finally, specify columns from both tables to include in the final result set in the SELECT clause:

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

PostgreSQL RIGHT JOIN clause example

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:

The following statement uses the RIGHT 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
  products
  RIGHT JOIN brands ON brands.brand_id = products.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 RIGHT 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 right table and columns from the left 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 row for all columns corresponding to columns in the products table, fills them will NULL and merges with the row in the brands table:

After examining all rows in the brands and products tables, 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
  RIGHT 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)

Using 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
  RIGHT JOIN products p ON p.brand_id = b.brand_id;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL RIGHT 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
  RIGHT JOIN right_table USING (column1);Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this statement, we use the USING clause instead of the ON clause in the RIGHT JOIN.

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

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

Summary

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