Great! Another JOIN type is the FULL JOIN. FULL JOIN is a union of LEFT JOIN and RIGHT JOIN. It shows matching rows from both tables, rows that have no match from the left table, and rows that have no match from the right table. Take a look at this example:
SELECT course.name lecturer.name FROM course FULL JOIN lecturer ON course.lecturer_id = lecturer.id
The result would be as follows:
| course_name | lecturer_name |
|---|---|
| English grammar | John Martinez |
| English listening | Edward McCullen |
| History of England | Justin Haar |
| Phonetics | null |
| Phonology | null |
| null | Marcin Wydrowski |
| null | Peter Par |
The pink rows are returned by the INNER JOIN. The green rows are returned by the LEFT JOIN. The blue rows are returned by the RIGHT JOIN.
Display the title of each book along with the name of its author. Show all books, even those without an author. Show all authors, even those who haven't published a book yet. Use a FULL JOIN.
Type:
SELECT title, name FROM book FULL JOIN author ON book.author_id = author.id
