🔹 LEFT JOIN in SQL
Definition:
The LEFT JOIN clause returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL from the right table.
Syntax:
SELECT table1.column, table2.column
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
Example:
SELECT Employees.name, Departments.department_name
FROM Employees
LEFT JOIN Departments
ON Employees.department_id = Departments.department_id;
Sample Employees Table:
| id | name | department_id |
|---|
| 1 | Alice | 101 |
| 2 | Bob | 102 |
| 3 | Charlie | 103 |
Sample Departments Table:
| department_id | department_name |
|---|
| 101 | HR |
| 102 | Engineering |
| 104 | Finance |
Output:
| name | department_name |
|---|
| Alice | HR |
| Bob | Engineering |
| Charlie | NULL |
Explanation:
The query returns all rows from the Employees table:
- Alice (dept 101) → HR
- Bob (dept 102) → Engineering
- Charlie (dept 103) → No match in Departments → **NULL**
Finance (id 104) has no employee → **not shown**
LEFT JOIN ensures every row from the left table appears, matched or not.
Usage Tips:
- Use
LEFT JOIN when you want all rows from the first (left) table regardless of matches.
- Very useful for finding unmatched or missing data in the right table.
Common Mistake:
Assuming LEFT JOIN works like INNER JOIN. It does not — unmatched left table rows are included with NULLs for right table columns.
📘 Note: LEFT JOIN is sometimes called LEFT OUTER JOIN; both mean the same.