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!
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.
Type:
SELECT * FROM student RIGHT JOIN room ON student.room_id = room.id;
