Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Get to know the data
JOIN revisited
LEFT JOIN
RIGHT JOIN
13. RIGHT JOIN explained
FULL JOIN
OUTER JOINS
NATURAL JOIN
Aliases for tables

Instruction

Great! As you may have guessed, there is also a RIGHT JOIN.

The RIGHT JOIN works in the following way: it returns all rows from the right table (the second table in the query) plus all matching rows from the left table (the first table in the query).

Let's see an example. Take a look at the query:

SELECT *
FROM car
RIGHT JOIN person
  ON car.owner_id = person.id;

RIGHT JOIN result is shown in the following table:

car person
id brand owner_id id name
1 Ford 3 3 Jane Preston
2 Opel 1 1 Cody Wright
3 Volkswagen 2 2 Oscar Walls
4 Toyota 4 4 Megan Black
null null null 5 Alice Cooper
null null null 6 James Brown
null null null 7 Charles Smith

The RIGHT JOIN returns all rows in the table above. The blue rows are returned by the INNER JOIN. The pink rows are added by the RIGHT JOIN.

Notice that the order of the tables in LEFT and RIGHT JOIN matters. In other words, car RIGHT JOIN person is the same as person LEFT JOIN car. Don't confuse the order!

Exercise

For each student show their data with the data of the room they live in. Show also rooms with no students assigned. Use a RIGHT JOIN.

Stuck? Here's a hint!

Type:

SELECT *
FROM student
RIGHT JOIN room
  ON student.room_id = room.id;