August 3, 2023
Summary: in this tutorial, you will learn how to use the PostgreSQL
RIGHT JOIN to select data from two tables.
Setting up sample tables
Suppose that you have two tables
film_reviews like this:
DROP TABLE IF EXISTS films; DROP TABLE IF EXISTS film_reviews; CREATE TABLE films( film_id SERIAL PRIMARY KEY, title varchar(255) NOT NULL ); INSERT INTO films(title) VALUES('Joker'), ('Avengers: Endgame'), ('Parasite'); CREATE TABLE film_reviews( review_id SERIAL PRIMARY KEY, film_id INT, review VARCHAR(255) NOT NULL ); INSERT INTO film_reviews(film_id, review) VALUES(1, 'Excellent'), (1, 'Awesome'), (2, 'Cool'), (NULL, 'Beautiful');
A film can have zero or many reviews and a review belongs to zero or one film. The
film_id column in the
films references the
film_id column in the
The following shows the contents of the
SELECT * FROM films;
SELECT * FROM film_reviews;
The film id 1 has two reviews. The film id 2 has 1 review. The film id 3 has no reviews. The review id 4 does not associate with any film.
Introduction to PostgreSQL RIGHT JOIN clause
The following statement uses the
RIGHT JOIN to select data from the
SELECT review, title FROM films RIGHT JOIN film_reviews ON film_reviews.film_id = films.film_id;
In this statement,
films is the left table and
film_reviews is the right table.
RIGHT JOIN clause starts selecting data from the right table (
For each row from the right table (
film_reviews), it checks if the value in the
film_id column of the
film_reviews table equals the value in the
film_id column of every row from the left table (
If they are equal, the
RIGHT JOIN creates a new row that contains columns from both tables specified in the
SELECT clause and includes this new row in the result set.
RIGHT JOIN still creates a new row that contains columns from both tables and includes this new row in the result set. However, it fills the columns from the left table (
In other words, the
RIGHT JOIN selects all rows from the right table whether or not they have matching rows from the left table.
Based on the data from the
- The review with id 1 matches with the film id 1.
- The review with id 2 matches with film id 2.
- The review with id 3 matches with the film id 2.
- The review with id 4 doesn’t match any film therefore the
titlecolumn is filled with NULL.
Note that the
RIGHT OUTER JOIN is the same as
RIGHT JOIN. The
OUTER keyword is optional
The following Venn diagram illustrates how the
RIGHT JOIN works:
PostgreSQL RIGHT JOIN with USING syntax
Because the joined column has the same name (
film_id), you can use the
USING syntax in the join predicate like this:
SELECT review, title FROM films RIGHT JOIN film_reviews USING (film_id);
This query returns the same result as if it used the
PostgreSQL RIGHT JOIN with WHERE clause
To find the rows from the right table that does not have any corresponding rows in the left table, you add a
WHERE clause like this:
SELECT review, title FROM films RIGHT JOIN film_reviews using (film_id) WHERE title IS NULL;
In this tutorial, you have learned how to use the PostgreSQL
RIGHT JOIN clause to join data from two tables.