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
14. Filtering with JOINS – continued


Great! You can use this technique to filter rows with no matching rows.

Suppose we want to get a list of lecturers that still do not have any course assigned:

FROM lecturer
LEFT JOIN course
  ON course.lecturer_id =
WHERE lecturer_id IS NULL

Notice that we added WHERE lecturer_id IS NULL line.

First, our database LEFT JOINs the table. Then, we simply inform the database that we'd like to select only those rows where for which the value of lecturer_id IS NULL (i.e. there is no matching course for a given lecturer). Note that the lecturer_id is a column in the right table, course. This value will be missing if there is no matching course.


Show the title and publish_year for books that do not have any reviews in the book_review table.

Stuck? Here's a hint!


FROM book
LEFT JOIN book_review
  ON = book_review.book_id
WHERE book_review.book_id IS NULL