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

Instruction

Excellent! Another joining method is FULL JOIN. This type of JOIN returns all rows from both tables and combines the rows when there is a match. In other words, FULL JOIN is a union of LEFT JOIN and RIGHT JOIN.

Let's see an example:

SELECT
  *
FROM Car
FULL JOIN Person
  ON Car.OwnerID = Person.ID;

The result may look like this:

Car Person
ID Brand OwnerID ID Name
1 Ford 3 3 Megan Donald
2 Opel 5 5 John Smith
3 Nissan 4 4 Andrew Black
4 Skoda null null null
5 BMW null null null
6 Citroen null null null
null null null 1 Sylvia Green
null null null 2 Martin Dean
null null null 6 Adam Scott

The red rows are the rows returned by INNER JOIN. The green rows are the rows that would be added by a LEFT JOIN, and the purple rows are the rows that would be added by a RIGHT JOIN.

A FULL JOIN returns all rows in the table above.

Exercise

FShow the room each student is assigned to. Include students without a room and rooms without students. Show all columns.