LEFT JOIN

---

title: “LEFT JOIN” part: 1 topic_number: 16 slug: “left-join” difficulty: “Beginner” prerequisites: “inner-join” —

LEFT JOIN

What Is It?

LEFT JOIN returns ALL rows from the left table, and matching rows from the right table. If there’s no match, the right table’s columns show as NULL. It’s perfect for “show me everything from table A, and related info from table B if it exists.”

Real-world analogy: A class roster showing all students (left table) and their optional club memberships (right table). Students without clubs still appear on the roster, just with “no club” (NULL).

---

Syntax Breakdown

SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

Key difference from INNER JOIN:

• INNER JOIN: Only rows with matches in BOTH tables
• LEFT JOIN: ALL rows from table1, plus matches from table2 (NULLs if no match)

---

Basic Example

Show ALL employees and their department names (including employees with no department):

SELECT 
    e.first_name,
    e.last_name,
    e.department_id,
    d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Expected output (partial):

first_name	last_name	department_id	department_name
Alice	Johnson	1	Engineering
Bob	Smith	1	Engineering
…	…	…	…
Paul	Garcia	NULL	NULL

Key observation: Paul Garcia has NULL department_id, so department_name is also NULL. But Paul still appears in the results!

With INNER JOIN, Paul would be excluded. With LEFT JOIN, Paul is included.

---

Going Deeper

Finding Unmatched Rows

Show employees who DON’T have a department assigned:

SELECT 
    e.first_name,
    e.last_name,
    e.department_id
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

What this does:

1. LEFT JOIN includes all employees
2. For employees with no matching department, d.department_id is NULL
3. WHERE filters for only those NULL cases

Expected output:

first_name	last_name	department_id
Paul	Garcia	NULL

Use case: “Find orphaned records” — employees not properly assigned to departments.

LEFT JOIN with Aggregates

Count how many employees are in each department, including departments with 0 employees:

SELECT 
    d.department_name,
    COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
ORDER BY employee_count DESC;

What this does:

• Starts with departments (left table)
• Joins employees (right table)
• Counts employees per department
• Departments with no employees show count = 0

Expected output (partial):

department_name	employee_count
Engineering	5
Marketing	3
Sales	3
…	…
Operations	1

With INNER JOIN, departments with 0 employees wouldn’t appear. With LEFT JOIN, they appear with count = 0 (or 1 if Noah is in Operations).

Pause and Predict: Why does COUNT(e.employee_id) work for counting, but COUNT(*) wouldn’t give accurate results?

Answer

`COUNT(e.employee_id)` counts non-NULL employee_ids. For departments with no employees, e.employee_id is NULL, so COUNT returns 0. `COUNT(*)` counts rows, including rows with all NULLs. Even departments with no employees would show count = 1 (the department row itself). **Always use COUNT(column) from the right table in LEFT JOINs!**

Multiple LEFT JOINs

Show all employees, their departments, and their managers — even if some info is missing:

SELECT 
    e.first_name AS employee_name,
    d.department_name,
    m.first_name AS manager_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
LEFT JOIN employees m ON e.manager_id = m.employee_id;

What this does:

• All employees appear
• Department name shows NULL if employee has no department
• Manager name shows NULL if employee has no manager

Expected output (partial):

employee_name	department_name	manager_name
Alice	Engineering	NULL
Paul	NULL	NULL
Bob	Engineering	Alice

---

Watch Out — Common Mistakes

Mistake #1: WHERE on Right Table Converts LEFT JOIN to INNER JOIN

--  WRONG — This becomes an INNER JOIN!
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_name = 'Engineering';

What happens: WHERE filters AFTER the join. Rows where d.department_name is NULL (like Paul) are excluded. You’ve accidentally turned it into an INNER JOIN!

The fix: If you want to filter the right table, do it in the ON clause:

-- • CORRECT — Filter in ON clause
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id 
    AND d.department_name = 'Engineering';

Or filter the left table in WHERE (this is safe):

-- • ALSO CORRECT
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE e.salary > 50000;  -- Filtering left table is fine

Mistake #2: COUNT(*) in LEFT JOIN Aggregates

--  WRONG for accurate counts
SELECT d.department_name, COUNT(*) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

Problem: COUNT(*) counts rows, including “empty” department rows. A department with 0 employees shows count = 1.

-- • CORRECT
SELECT d.department_name, COUNT(e.employee_id) AS employee_count
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name;

COUNT(e.employee_id) counts non-NULL values. Departments with no employees show count = 0.

Mistake #3: LEFT vs RIGHT Table Order Matters!

-- These are DIFFERENT!

-- Version A: All employees, optional departments
SELECT e.first_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Version B: All departments, optional employees
SELECT e.first_name, d.department_name
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id;

Version A: Paul (no department) appears. Departments with no employees don’t appear.

Version B: All departments appear. Paul (no department) doesn’t appear.

Lesson: Choose your left table carefully — that’s the one where ALL rows will appear.

---

Edge Case Spotlight

LEFT JOIN with NULL in the Join Condition

SELECT e.first_name, m.first_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Alice has manager_id = NULL. The ON condition NULL = m.employee_id never matches (NULL doesn’t equal anything), so Alice gets NULL for manager_name.

Result for Alice:

first_name	manager_name
Alice	NULL

This is correct! Alice has no manager (she’s the CEO), so showing NULL makes sense.

---

Try This

Exercise 1 (Guided)

Show all projects and count how many employees are assigned to each (including projects with 0 employees). Show project_name and employee_count.

Hint

Start with projects (left table), LEFT JOIN employee_projects (right table), GROUP BY project_name, use COUNT on a column from the right table.

Exercise 2 (Independent)

Find all departments that have NO employees assigned. Show only the department_name.

Hint

LEFT JOIN from departments to employees, WHERE employee_id IS NULL.

Exercise 3 (Challenge)

Show all employees (even those not on projects) with a count of how many projects they’re assigned to. Show first_name, last_name, and project_count. Sort by project_count descending.

---

Answer Key

Exercise 1 Answer

```sql SELECT p.project_name, COUNT(ep.employee_id) AS employee_count FROM projects p LEFT JOIN employee_projects ep ON p.project_id = ep.project_id GROUP BY p.project_name ORDER BY employee_count DESC; ``` **Expected output (partial):** | project_name | employee_count | |--------------|----------------| | Website Redesign | 3 | | Mobile App Launch | 3 | | ... | ... | | Security Audit | 2 | | Legal Compliance Review | 1 | All 15 projects appear, even if they have 0 employees assigned.

Exercise 2 Answer

```sql SELECT d.department_name FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.employee_id IS NULL; ``` **Expected output:** Depends on the data, but potentially departments with no employees assigned. If all departments have at least one employee, this returns an empty set. **Alternative using NOT EXISTS (more efficient):** ```sql SELECT d.department_name FROM departments d WHERE NOT EXISTS ( SELECT 1 FROM employees e WHERE e.department_id = d.department_id ); ```

Exercise 3 Answer

```sql SELECT e.first_name, e.last_name, COUNT(ep.project_id) AS project_count FROM employees e LEFT JOIN employee_projects ep ON e.employee_id = ep.employee_id GROUP BY e.employee_id, e.first_name, e.last_name ORDER BY project_count DESC; ``` **Expected output (partial):** | first_name | last_name | project_count | |------------|-----------|---------------| | Bob | Smith | 4 | | Jack | Anderson | 3 | | Grace | Wilson | 2 | | ... | ... | ... | | Noah | Harris | 0 | | Paul | Garcia | 0 | Employees not on any projects show project_count = 0.

---

Quick Recap

• LEFT JOIN returns ALL rows from the left table
• Right table columns show NULL if there’s no match
• Perfect for finding “orphaned” or unmatched records
• Use COUNT(right_table_column) to count matches (not COUNT(*))
• WHERE on right table converts LEFT JOIN to INNER JOIN — use ON instead
• Left table choice matters — that’s the one that’s complete

---

Up Next

Next topic: RIGHT JOIN → part1_17_right_join.md

You’ve mastered INNER JOIN and LEFT JOIN. Next, RIGHT JOIN — which is just LEFT JOIN backwards!