Great! As you may have guessed, there is also a RIGHT JOIN.
The RIGHT JOIN 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). It is effectively the opposite of a LEFT JOIN.
Let's see an example. Take a look at the query:
SELECT * FROM Car RIGHT JOIN Person ON Car.OwnerID = Person.ID;
The RIGHT JOIN result is shown in the following table:
| Car | Person | |||
|---|---|---|---|---|
| Id | Brand | OwnerId | 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!
Show all information for each student and the room they live in. Show all rooms, even ones with no students assigned to them. Note: while this can be accomplished with a LEFT JOIN by reordering the tables, you should use a RIGHT JOIN for this exercise.
Type:
SELECT * FROM Student RIGHT JOIN Room ON Student.RoomID = Room.ID;
