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
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 returned by the RIGHT JOIN.
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_review and book 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.
Type:
SELECT book.title, book_review.review, book_review.author FROM book_review RIGHT JOIN book ON book.id = book_review.book_id
