Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Simple JOINs recap
Various kinds of JOINs
9. LEFT JOIN
Filtering with LEFT JOIN and RIGHT JOIN
Summary

Instruction

Great! Another common 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 LecturerId) 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 CourseName
  Lecturer.Name AS LecturerName
FROM Course
LEFT JOIN Lecturer
  ON Course.LecturerId = 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:

CourseName LecturerName
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.ReleaseYear
FROM Book
LEFT JOIN Adaptation
  ON Book.Id = Adaptation.BookId