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
lecturer (with columns
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:
course.name AS course_name
lecturer.name AS lecturer_name
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:
|History of England
The pink rows are returned by the
INNER JOIN. The green and pink rows are returned by the