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.
Show the room data which each student is assigned to. Include students without a room and rooms without students. Show all columns.
