Best April deals - hours only!Up to 80% off on all courses and bundles.-Close
Get to know the data
JOIN revisited
LEFT JOIN
RIGHT JOIN
FULL JOIN
15. FULL JOIN explained
OUTER JOINS
NATURAL JOIN
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.owner_id = person.id;

The result may look like this:

car person
id brand owner_id 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 pink rows are returned by an INNER JOIN.
The blue rows would be added to pink rows by a LEFT JOIN.
The purple rows would be added to pink rows by a RIGHT JOIN.

A FULL JOIN returns all rows from the table above.

Exercise

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