Simple JOINs recap
Various kinds of JOINs
10. RIGHT JOIN
Filtering with LEFT JOIN and RIGHT JOIN
Summary

Instruction

Great! If there is a LEFT JOIN, there must be a RIGHT JOIN, too, right? What's the difference? Well, recall that a LEFT JOIN shows all rows from the left table, even if there are no matching rows for the ON condition in the right table. Similarly a RIGHT JOIN shows all rows from the right table, even if there are no matching rows for the ON condition in the left table..

Suppose that this time, we want to show all lecturers and the courses they are teaching; if a lecturer has not yet been assigned to a course, we still want to show their name. We can do that like this:

SELECT
  course.name AS course_name
  lecturer.name AS lecturer_name
FROM course
RIGHT JOIN lecturer
ON course.lecturer_id = lecturer.id

This example would produce the following result:

course_name: lecturer_name
English grammar John Martinez
English listening Edward McCullen
History of England Justin Haar
null Marcin Wydrowski
null Peter Par

The pink rows are returned by the INNER JOIN. The blue and pink rows are are returned by the RIGHT JOIN.

Exercise

We've added a new table: book_review. It's simple and contains only three columns:

  • book_id - the ID of a reviewed book,
  • review - the summary of the review.
  • author - the name of the review's author.

Join the book and book_review tables using a RIGHT JOIN. Show the title of the book, the corresponding review, and the name of the review's author. Consider all books, even those that weren't reviewed.

Stuck? Here's a hint!

Type:

SELECT
  book.title,
  book_review.review,
  book_review.author
FROM book_review
RIGHT JOIN book
  ON book.id = book_review.book_id

Console

Code editor

Result

TableConsole