Introduction to PostgreSQL RIGHT JOIN clause

TheRIGHT JOIN clause joins a right table with a left table and returns the rows from the right table that may or may not have matching rows in the left table.

TheRIGHT JOIN can be useful when you want to find rows in the right table that do not have matching rows in the left table.

Here’s the basic syntax of theRIGHT JOIN clause:

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

In this syntax:

• First, specify the columns from both tables in theselect_list in theSELECT clause.
• Second, provide the left table (table1) from which you want to select data in theFROM clause.
• Third, specify the right table (table2) that you want to join with the left table in theRIGHT JOIN clause.
• Finally, define a condition for joining two tables (table1.column_name = table2.column_name), which indicates thecolumn_name in each table should have matching rows.

How the RIGHT JOIN works

TheRIGHT JOIN starts retrieving data from the right table (table2).

For each row in the right table (table2), theRIGHT JOIN checks if the value in thecolumn_name is equal to the value of the corresponding column in every row of the left table (table1).

When these values are equal, theRIGHT JOIN creates a new row that includes columns specified in theselect_list and appends it to the result set.

If these values are not equal, theRIGHT JOIN generates a new row that includes columns specified in theselect_list, populates the columns on the left withNULL, and appends the new row to the result set.

In other words, theRIGHT JOIN returns all rows from the right table whether or not they have corresponding rows in the left table.

The following Venn diagram illustrates how theRIGHT JOIN works:

Note that theRIGHT OUTER JOIN is the same asRIGHT JOIN. TheOUTER keyword is optional

The USING syntax

When the columns for joining have the same name, you can use theUSING syntax:

SELECT  select_listFROM  table1RIGHT JOIN table2USING (column_name);

PostgreSQL RIGHT JOIN examples

We’ll use thefilm andinventory tables from thesample database.

1) Basic PostgreSQL RIGHT JOIN examples

The following example uses theRIGHT JOIN clause to retrieve all rows from the film table that may or may not have corresponding rows in the inventory table:

SELECT  film.film_id,  film.title,  inventory.inventory_idFROM  inventoryRIGHT JOIN film  ON film.film_id= inventory.film_idORDER BY  film.title;

Output:

You can rewrite the above query using table aliases:

SELECT  f.film_id,  f.title,  i.inventory_idFROM  inventory iRIGHT JOIN film f  ON f.film_id = i.film_idORDER BY  f.title;

Since the film and inventory table has the film_id column, you can use the USING syntax:

SELECT  f.film_id,  f.title,  i.inventory_idFROM  inventory iRIGHT JOIN film fUSING(film_id)ORDER BY  f.title;

2) PostgreSQL RIGHT JOIN with a WHERE clause

The following query uses aRIGHT JOIN clause with aWHERE clause to retrieve the films that have no inventory:

SELECT  f.film_id,  f.title,  i.inventory_idFROM  inventory iRIGHT JOIN film fUSING(film_id)WHERE i.inventory_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 PostgreSQLRIGHT JOIN clause to join a right table with a left table and return rows from the right table that may or may not have corresponding rows in the left table.
• TheRIGHT JOIN is also known asRIGHT OUTER JOIN.

Last updated onJanuary 18, 2024

On this page

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