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


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 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 AS course_name AS lecturer_name
FROM course
LEFT JOIN lecturer
  ON course.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.


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!


FROM book
LEFT JOIN adaptation
  ON = adaptation.book_id