LEFT JOIN and Missing Data

Keep every row on one side, even when its partner is missing

Big Idea: An INNER JOIN only shows rows that have a match in both tables. A LEFT JOIN keeps every row from the first (left) table, and where there is no match it simply fills the other columns with NULL. This lets you spot who or what is missing.

By the end you will be able to:

explain how a LEFT JOIN differs from an INNER JOIN,
read the NULL values that appear when a row has no match,
use a RIGHT JOIN and know it is just a mirror of LEFT JOIN,
find rows that have no match with the LEFT JOIN ... IS NULL pattern.

1. A quick reminder: INNER JOIN

In the last topic you learned the INNER JOIN. It glues two tables together on a matching column, and it only keeps a row when a match exists on both sides. If a student has no book, the student vanishes from the result. That is great when you want only the pairs that match, but sometimes the missing rows are exactly what you care about.

Think about a teacher who wants a list of every student, with their borrowed books shown next to them. Some students have not borrowed anything yet, but the teacher still wants their names on the list. An INNER JOIN would quietly drop those students. A LEFT JOIN keeps them.

2. Our example: a tiny school library

We will use two small tables all the way through. A students table and a loans table (one row per book a student has borrowed).

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name       VARCHAR(50),
    grade      INT
);

CREATE TABLE loans (
    loan_id    INT PRIMARY KEY,
    student_id INT,
    book       VARCHAR(50)
);

INSERT INTO students VALUES
    (1, 'Amina', 7),
    (2, 'Ben',   7),
    (3, 'Chloe', 8),
    (4, 'Diego', 8),
    (5, 'Emma',  7);

INSERT INTO loans VALUES
    (101, 1, 'Treasure Island'),
    (102, 1, 'Matilda'),
    (103, 3, 'The Hobbit'),
    (104, 4, 'Holes');

Here is what each table holds. Notice that Ben and Emma have not borrowed any book, so no row in loans mentions their student_id (2 or 5).

students

student_id	name	grade
1	Amina	7
2	Ben	7
3	Chloe	8
4	Diego	8
5	Emma	7

loans

loan_id	student_id	book
101	1	Treasure Island
102	1	Matilda
103	3	The Hobbit
104	4	Holes

LEFT JOIN and Missing Data