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 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!

Exercise

Click Next exercise to continue.