Get to know the data
FULL JOIN
OUTER JOINS
NATURAL JOIN
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.
