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 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.
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.
Type:
SELECT book.title, adaptation.title, adaptation.release_year FROM book LEFT JOIN adaptation ON book.id = adaptation.book_id
