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

Instruction

Good job! As you remember, you can combine INNER JOIN with WHERE without problems.

However, using WHERE with a LEFT JOIN, RIGHT JOIN or FULL JOIN can sometimes be tricky. Let's see an example. Suppose you want to select all courses and show only those lecturers who were employed after 2004. Here's the query:

SELECT
  course.name,
  lecturer.name
FROM course
LEFT JOIN lecturers lecturer
ON course.lecturer_id = lecturer.id
WHERE lecturer.emp_year > 2004

Imagine that a particular course (e.g. "SQL Joins") doesn't have a lecturer assigned yet. This course will NOT show up in the result of the query. Why? Since the course doesn't have a lecturer, the column lecturer.emp_year is null and the condition lecturer.emp_year > 2004 is not true.

If you use a condition on the left table the LEFT JOIN can yield the same result as an INNER JOIN. If you want to keep the rows with no lecturer in the result, you must explicitly include empty (null) values:

SELECT
  course.name,
  lecturer.name
FROM course
LEFT JOIN lecturers lecturer
  ON course.lecturer_id = lecturer.id
WHERE lecturer.emp_year > 2004
  OR lecturer.emp_year IS NULL

Exercise

Show all books with their movie adaptations. Select each book's title, the name of its publishing house, the title of its adaptation, and the type of the adaptation.

Stuck? Here's a hint!

Type:

SELECT
  book.title,
  publishing_house,
  adaptation.title,
  adaptation.type
FROM book
LEFT JOIN adaptation
  ON book.id = adaptation.book_id
WHERE type = 'movie'

Console

Code editor

Result

TableConsole