LEFT JOIN

Learn how LEFT JOIN keeps every row from the left table, filling in NULLs when there is no match on the right.

Introduction

In the last lesson, INNER JOIN quietly dropped Owen from the results because he had no enrollments. Most of the time that is fine — but sometimes you specifically want to see every row from one table, whether or not it has a match on the other side. That is exactly what LEFT JOIN is for.

This lesson covers LEFT JOIN (also written LEFT OUTER JOIN), one of the most useful joins for reporting-style questions like "which students have NOT enrolled in anything yet?"

What You Will Learn

What LEFT JOIN does and how it differs from INNER JOIN.
Why unmatched rows get filled in with NULL instead of being dropped.
A full worked example showing every student, including ones with no enrollments.
When to reach for LEFT JOIN instead of INNER JOIN.

What is a LEFT JOIN?

A LEFT JOIN returns ALL rows from the left table (the one listed first, right after FROM), along with matching rows from the right table. When there is no match on the right side, MySQL still keeps the row from the left table — it just fills every column that would have come from the right table with NULL.

LEFT JOIN and LEFT OUTER JOIN are exactly the same thing in MySQL; OUTER is optional and can be left out.

Simple Definition

LEFT JOIN keeps every row from the left table, no matter what.
Matching rows from the right table are attached where they exist.
Non-matching rows get NULL in every right-table column.

The Sample Tables

We continue using the same students and enrollments tables from the previous lesson.

setup.sql

-- students
-- id | name
--  1 | Alex
--  2 | Sam
--  3 | Priya
--  4 | Owen

-- enrollments
-- id  | student_id | course_name
-- 101 | 1          | MySQL Basics
-- 102 | 1          | Web Design
-- 103 | 2          | MySQL Basics
-- 104 | 3          | JavaScript 101

Remember: Owen (id 4) has no rows in enrollments at all.

Basic Syntax

SELECT columns
FROM left_table
LEFT JOIN right_table
    ON left_table.column = right_table.column;

Whichever table is written directly after FROM is the "left" table — every one of its rows is guaranteed to appear at least once in the result.

Worked Example

Let's list every student, along with their course_name if they have one — including students who are not enrolled in anything.

left_join.sql

SELECT s.name, e.course_name
FROM students AS s
LEFT JOIN enrollments AS e
    ON s.id = e.student_id;

Result

+-------+-----------------+
| name  | course_name     |
+-------+-----------------+
| Alex  | MySQL Basics    |
| Alex  | Web Design      |
| Sam   | MySQL Basics    |
| Priya | JavaScript 101  |
| Owen  | NULL            |
+-------+-----------------+

This time Owen appears in the result, exactly once, with NULL in the course_name column. MySQL is telling us: this row from students exists, but there was nothing to match it to in enrollments.

Note

A NULL here does not mean the course name is literally the text "NULL" — it represents the absence of any value. Comparing course_name = 'NULL' would not match it; you would need "course_name IS NULL" instead, which is covered in an upcoming lesson.

LEFT JOIN vs INNER JOIN

The difference comes down to a single question: do you want to lose rows that have no match, or keep them with NULLs filled in?

INNER JOIN

Only students who have at least one enrollment.

Drops Owen completely.
Good for "show me only matched data."
Result: 4 rows (Alex x2, Sam, Priya).

LEFT JOIN

Every student, matched or not.

Keeps Owen, with NULL course_name.
Good for "show me everything, matched or not."
Result: 5 rows (Alex x2, Sam, Priya, Owen).

A good rule of thumb: use INNER JOIN when a row without a match is not useful to your question. Use LEFT JOIN when the left-side row still matters even if nothing matches it on the right — like finding customers with no orders, or in this case, students with no enrollments.

Finding Unmatched Rows Only

A very common pattern is to combine LEFT JOIN with a WHERE clause that checks for NULL, to find only the rows that have NO match at all — for example, "which students are not enrolled in anything?"

unenrolled_students.sql

SELECT s.name
FROM students AS s
LEFT JOIN enrollments AS e
    ON s.id = e.student_id
WHERE e.student_id IS NULL;

Result

+------+
| name |
+------+
| Owen |
+------+

This pattern — LEFT JOIN plus "WHERE right_table.column IS NULL" — is one of the most useful tricks in SQL for finding "orphaned" or unmatched records.

Common Mistakes

Avoid These Mistakes

Filtering on a right-table column in WHERE (e.g. WHERE e.course_name = 'MySQL Basics') without realizing it silently turns the query back into something like an INNER JOIN, since NULL rows fail that condition.
Getting the left and right tables backwards, which changes which rows are guaranteed to survive.
Forgetting that unmatched right-table columns are NULL, not empty strings or zero.
Using = NULL instead of IS NULL when filtering for unmatched rows.

Best Practices

Reach for LEFT JOIN whenever the left table's rows matter even without a match.
Put any condition on the right table's columns inside the ON clause, not WHERE, unless you specifically want to filter out unmatched rows.
Use the LEFT JOIN + IS NULL pattern to find missing or orphaned relationships.
Read queries left to right: FROM decides which table's rows are always kept.

Frequently Asked Questions

Is LEFT JOIN the same as LEFT OUTER JOIN?

Yes, they are identical in MySQL. The word OUTER is optional and commonly omitted.

Does LEFT JOIN ever return fewer rows than the left table has?

No. Every row in the left table appears at least once, though it may appear multiple times if it matches multiple rows on the right.

Why are the unmatched columns NULL instead of blank?

NULL specifically represents "no value exists," which is different from an empty string or zero, so MySQL uses NULL to be precise about the absence of a match.

Can I use LEFT JOIN with more than two tables?

Yes, you can chain multiple LEFT JOIN clauses just like with INNER JOIN, each with its own ON clause.

When should I NOT use LEFT JOIN?

When you only care about rows that definitely exist on both sides — in that case INNER JOIN is simpler and clearer about your intent.

Key Takeaways

LEFT JOIN keeps every row from the left table, matched or not.
Unmatched rows get NULL in every column from the right table.
LEFT JOIN + WHERE right.column IS NULL finds rows with no match at all.
Use INNER JOIN when unmatched rows are not useful; use LEFT JOIN when they still matter.
LEFT OUTER JOIN and LEFT JOIN mean exactly the same thing in MySQL.

Summary

LEFT JOIN is the join to reach for whenever you need "everything from this table, whether or not it has a match elsewhere." It preserves every row on the left and fills in NULL wherever the right table has nothing to offer.

In this lesson, you learned how LEFT JOIN differs from INNER JOIN, saw a worked example including an unmatched student, and learned the LEFT JOIN + IS NULL pattern for finding missing relationships. Next, you will learn RIGHT JOIN, the mirror image of LEFT JOIN.

Lesson 48 Completed

You understand how LEFT JOIN preserves every left-table row.
You can explain why unmatched columns come back as NULL.
You know the LEFT JOIN + IS NULL trick for finding unmatched rows.
You are ready to learn RIGHT JOIN.

Next Lesson →