Get to know the data
JOIN revisited
LEFT JOIN
RIGHT JOIN
13. RIGHT JOIN explained
FULL JOIN
OUTER JOINS
Aliases for tables

Instruction

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 red rows are the rows returned by an INNER JOIN. The green rows are the rows added by a RIGHT JOIN.

Notice that the order of the tables in LEFT JOIN and RIGHT JOIN does matter. In other words, Car RIGHT JOIN Person is the same as Person LEFT JOIN Car. Don't confuse the order!

Exercise

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.

Stuck? Here's a hint!

Type:

SELECT
  *
FROM Student
RIGHT JOIN Room
  ON Student.RoomID = Room.ID;