Course contentsShow
← Computer Science
Lesson 1579 of 2,87236. Database FundamentalsFree lesson

Left Outer Join

Understanding LEFT JOIN to include all rows from the left table, with NULLs for non-matches.

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.