Simple JOINs recap
Various kinds of JOINs
11. FULL JOIN
Filtering with LEFT JOIN and RIGHT JOIN
Summary

Instruction

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.

Exercise

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.

Stuck? Here's a hint!

Type:

SELECT
  title,
  name
FROM book
FULL JOIN author
  ON book.author_id = author.id

Console

Code editor

Result

TableConsole