LEFT JOIN in SQL

🔵 Shaded = all of employees (left table) is kept, matched or not.

Reference Tables Used in This Section

Every join note below uses these same two tables, so you can see exactly how each join type treats the same data differently.

employees

departments

Notice: Neha has no department (department_id is NULL), and Sales has no employees at all. These two "unmatched" rows are what make the differences between join types visible.

Syntax

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

Example

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

Expected Output:

All 4 employees appear — Neha included, with NULL in department_name since she has no matching department. Sales still doesn't appear, since it's on the (unkept) right side with no employees.

Finding Unmatched Rows — A Very Common Pattern

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

Output: Neha. This "LEFT JOIN + WHERE right.key IS NULL" pattern is the standard way to find rows in one table that have no corresponding row in another — customers with no orders, products never sold, students not enrolled in any course.

Practical Use Case

Whenever the left table's rows all matter, regardless of whether related data exists — "list every employee, with their department if assigned" is the exact business phrasing that signals LEFT JOIN.

Common Mistakes

• Putting the unmatched-row filter in WHERE incorrectly. WHERE d.department_name = 'Engineering' after a LEFT JOIN silently turns it back into something like an inner join for that condition, because non-matching rows have NULL there and fail the filter. If you need to filter the right table while still keeping unmatched left rows, move the condition into the ON clause instead.
• Confusing which table is "left" — it's determined by which table appears before LEFT JOIN in the FROM clause, not by any inherent property of the table

Interview Relevance

"Find all customers who have never placed an order" is a near-universal SQL interview question, and it's solved with exactly the LEFT JOIN + IS NULL pattern shown above.

Practice Question

Write a query to find every department that currently has zero employees, using a LEFT JOIN starting from departments.

Related SQL Notes

• RIGHT JOIN
• INNER JOIN vs LEFT JOIN
• LEFT JOIN vs RIGHT JOIN