LEFT JOIN

In the previous lesson, we learned about INNER JOIN, which returns only matching records from both tables.

In this lesson, we will explore LEFT JOIN, which behaves differently and is extremely important in real-world database queries.

What is LEFT JOIN?

A LEFT JOIN returns:

• All rows from the left table
• Matching rows from the right table
• NULL values when no match exists

If there is no matching row in the right table, the left table row still appears in the result.

LEFT JOIN Syntax

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

The table written before LEFT JOIN is the left table.

Example Tables

employees (Left Table)

+----+----------+---------------+--------+
| id | name     | department_id | salary |
+----+----------+---------------+--------+
| 1  | Alice    | 101           | 75000  |
| 2  | Bob      | 102           | 72000  |
| 3  | Charlie  | 101           | 60000  |
| 4  | Diana    | 104           | 80000  |
+----+----------+---------------+--------+
  

departments (Right Table)

+---------------+----------------+
| department_id | department_name|
+---------------+----------------+
| 101           | IT             |
| 102           | HR             |
| 103           | Finance        |
+---------------+----------------+
  

LEFT JOIN Example

Retrieve all employees along with their department names.

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

Result explanation:

• Alice → IT
• Bob → HR
• Charlie → IT
• Diana → NULL

Diana appears even though her department does not exist in the departments table.

Understanding NULL in LEFT JOIN

NULL indicates that no matching record was found in the right table.

This does not mean the data is missing — it means the relationship does not exist.

LEFT JOIN with WHERE (Important)

Be careful when using WHERE with LEFT JOIN. Incorrect placement can turn it into an INNER JOIN.

❌ This removes NULL rows:

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
WHERE d.department_name = 'IT';
  

✅ Correct way (preserves LEFT JOIN behavior):

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d
ON e.department_id = d.department_id
AND d.department_name = 'IT';
  

Finding Unmatched Rows

LEFT JOIN is often used to find records that do not have a match.

Example: Employees without a department.

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

This is a very common real-world use case.

LEFT JOIN vs INNER JOIN

Common Beginner Mistakes

• Forgetting which table is left
• Filtering NULLs incorrectly
• Using WHERE instead of ON for join conditions
• Expecting LEFT JOIN to behave like INNER JOIN

When to Use LEFT JOIN

Use LEFT JOIN when:

• You want all records from the main table
• Missing relationships are important
• You need to detect unmatched data

What’s Next?

In the next lesson, we will explore RIGHT JOIN and compare it with LEFT JOIN.