Summary: in this tutorial, you will how to use PostgreSQL RIGHT JOIN
to join two tables and return rows from the right table that may or may not have matching rows in the left table.
Introduction to PostgreSQL RIGHT JOIN clause
The RIGHT 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.
The RIGHT 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 the RIGHT JOIN
clause:
SELECT
select_list
FROM
table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Code language: SQL (Structured Query Language) (sql)
In this syntax:
- First, specify the columns from both tables in the
select_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
The RIGHT JOIN
starts retrieving data from the right table (table2
).
For each row in the right table (table2
), the RIGHT JOIN
checks if the value in the column_name
is equal to the value of the corresponding column in every row of the left table (table1
).
When these values are equal, the RIGHT JOIN
creates a new row that includes columns specified in the select_list
and appends it to the result set.
If these values are not equal, the RIGHT JOIN
generates a new row that includes columns specified in the select_list
, populates the columns on the left with NULL
, and appends the new row to the result set.
In other words, the RIGHT 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 the RIGHT JOIN
works:
![PostgreSQL Join - Right Join](https://www.postgresqltutorial.com/wp-content/uploads/2018/12/PostgreSQL-Join-Right-Join.png)
Note that the RIGHT OUTER JOIN
is the same as RIGHT JOIN
. The OUTER
keyword is optional
The USING syntax
When the columns for joining have the same name, you can use the USING
syntax:
SELECT
select_list
FROM
table1
RIGHT JOIN table2 USING (column_name);
Code language: SQL (Structured Query Language) (sql)
PostgreSQL RIGHT JOIN examples
We’ll use the film
and inventory
tables from the sample database.
1) Basic PostgreSQL RIGHT JOIN examples
The following example uses the RIGHT 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_id
FROM
inventory
RIGHT JOIN film
ON film.film_id = inventory.film_id
ORDER BY
film.title;
Output:
![PostgreSQL RIGHT JOIN example](https://www.postgresqltutorial.com/wp-content/uploads/2024/01/PostgreSQL-RIGHT-JOIN-example.png)
You can rewrite the above query using table aliases:
SELECT
f.film_id,
f.title,
i.inventory_id
FROM
inventory i
RIGHT JOIN film f
ON f.film_id = i.film_id
ORDER BY
f.title;
Code language: SQL (Structured Query Language) (sql)
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_id
FROM
inventory i
RIGHT JOIN film f USING(film_id)
ORDER BY
f.title;
Code language: SQL (Structured Query Language) (sql)
2) PostgreSQL RIGHT JOIN with a WHERE clause
The following query uses a RIGHT JOIN
clause with a WHERE
clause to retrieve the films that have no inventory:
SELECT
f.film_id,
f.title,
i.inventory_id
FROM
inventory i
RIGHT JOIN film f USING(film_id)
WHERE i.inventory_id IS NULL
ORDER BY
f.title;
Code language: SQL (Structured Query Language) (sql)
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
...
Code language: JavaScript (javascript)
Summary
- Use the PostgreSQL
RIGHT 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. - The
RIGHT JOIN
is also known asRIGHT OUTER JOIN
.