PostgreSQL Tutorial: RIGHT JOIN

Summary: in this tutorial, you will learn how to use the PostgreSQL RIGHT JOIN to select data from two tables.

Table of Contents

Setting up sample tables

Suppose that you have two tables films and 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 film_reviews table.

The following shows the contents of the films and film_reviews tables:

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 films and film_reviews tables:

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.

The RIGHT JOIN clause starts selecting data from the right table (film_reviews).

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 (films).

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.

Otherwise, the 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 (films) with NULL.

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 films and film_reviews tables:

• 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 title column 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 ON clause.

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.