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

Instruction

Great! Another comomn type of JOIN is the LEFT JOIN. It is used when we want to return ALL rows from the table to which we are joining another table, even if there is no matching row in the second table.

Imagine you have two tables: course (with columns id, name and lecturer_id) and lecturer (with columns id and name). Some courses do not have lecturers yet, as the term is still being planned. You'd like to list all courses and the lecturers teaching team, including courses that don't yet have lecturer assigned.

Here's how you would do that:

SELECT
  course.name AS course_name
  lecturer.name AS lecturer_name
FROM course
LEFT JOIN lecturer
ON course.lecturer_id = lecturer.id

LEFT JOIN tells the database to list ALL rows from the left table (course), even if there is no matching row for the ON condition in the right table (lecturer). That way, you'll see all courses, even if the course doesn't have a lecturer yet.

The database will display NULL for the missing rows:

course_name lecturer_name
English grammar John Martinez
English listening Edward McCullen
History of England Justin Haar
Phonetics null
Phonology null

The pink rows are returned by the INNER JOIN. The green and pink rows are returned by the LEFT JOIN.

Exercise

Show the title of each book together with the name of its adaptation and the date of the release.

Show all books, regardless of whether they had adaptations.

Stuck? Here's a hint!

Type:

SELECT
  book.title,
  adaptation.title,
  adaptation.release_year
FROM book
LEFT JOIN adaptation
ON book.id = adaptation.book_id

Console

Code editor

Result

TableConsole