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 INNER JOIN.
The blue rows would be added by a LEFT JOIN.
The purple rows would be added by a RIGHT JOIN.
A FULL JOIN returns all rows from the table above.
Unfortunately, some databases don't support FULL JOINs and this is exactly the problem with the database we're working on in this SQL course. In other words, we'll let you off without an exercise this time!
Click to continue.
