Left Outer Join
What You'll Learn
You'll learn how a LEFT JOIN retrieves all rows from the first table, even when there are no matching rows in the second table.
The Core Idea
An Inner Join only shows rows where matches exist in both tables. But what if you want to see all rows from one table, regardless of whether they have partners in the other?
That's where LEFT JOIN (also called LEFT OUTER JOIN) comes in. It keeps every row from the left (first) table in your query, and fills in data from the right (second) table where matches exist. When no match is found, SQL inserts NULL values for all columns from the right table.
How It Works
Think of it like a class roster with optional activity sign-ups. You want to see every student (left table), along with which activity they signed up for (right table). Students who didn't sign up still appear on your list—they just have "none" listed for their activity.
SELECT students.name, activities.activity_name
FROM students
LEFT JOIN activities ON students.id = activities.student_id;
This query returns:
- All students who signed up for activities (with their activity names shown)
- All students who didn't sign up (with NULL in the activity_name column)
When to Use LEFT JOIN
Use LEFT JOIN when you need to:
- Preserve all records from your primary table
- Identify which records have no related data (by checking for NULLs)
- Create reports showing "complete" vs "incomplete" relationships
Key Takeaway: LEFT JOIN includes every row from the left table, pairing it with matching right-table data when available, or NULLs when no match exists—perfect for finding both connections and gaps in your data.