INNER JOIN quietly dropped Legal, the department with no employees. LEFT JOIN is built for exactly this situation: it keeps every row from the left table (the one named right after FROM) no matter what, filling in NULL for any right-table columns that have no match.
Learning Objectives
- Write a LEFT JOIN and predict which table's rows are guaranteed to appear.
- Read NULL in a LEFT JOIN result as "no match found," not as an error.
- Use LEFT JOIN plus IS NULL to find rows with no match at all, like Legal's empty department.
departments (left)
All 5 rows, including Legal
LEFT JOIN
Keeps every left row
employees (right)
Matched where possible
Result
Legal appears with NULL employee columns
Finding Departments With No Employees
departments is on the left (named right after FROM), so every department appears in the result at least once, no matter what. Legal shows up with first_name as NULL, there's no matching employee row, but the department row itself is never dropped the way INNER JOIN would drop it.
SELECT d.name AS department_name, e.first_name FROM departments d LEFT JOIN employees e ON e.department_id = d.id;
Isolating Only the Unmatched Rows
This is a genuinely common real pattern: LEFT JOIN, then WHERE right_table.some_column IS NULL, filters the result down to only the left rows that had no match at all, departments with zero employees, in this case just Legal. e.id specifically (not e.first_name) is checked because it's the right table's own primary key, guaranteed NULL only when nothing matched, never NULL for a real row.
SELECT d.name AS department_name FROM departments d LEFT JOIN employees e ON e.department_id = d.id WHERE e.id IS NULL;
LEFT JOIN With Three Tables
Every employee appears at least once, even one assigned to no projects at all shows up with project_name as NULL, instead of disappearing the way an INNER JOIN chain would make them disappear.
SELECT e.first_name, p.name AS project_name FROM employees e LEFT JOIN project_assignments pa ON pa.employee_id = e.id LEFT JOIN projects p ON pa.project_id = p.id;
Try It Yourself
Write a query that lists every department name next to a count placeholder, including departments with no employees. (Just the join, counting with GROUP BY comes in Module 5.)
SELECT d.name AS department_name, e.first_name FROM departments d LEFT JOIN employees e ON e.department_id = d.id ORDER BY d.name;
Frequently Asked Questions
Is LEFT JOIN the same as LEFT OUTER JOIN?
Yes, identical, OUTER is optional and rarely written out in practice. LEFT JOIN alone means the same thing in every database in this course.
Does the left/right table order actually matter here?
Yes, unlike INNER JOIN. departments LEFT JOIN employees keeps every department; employees LEFT JOIN departments would instead keep every employee (and would never show Legal, since Legal has no employees to keep).
Common Beginner Mistakes
Filtering on the right table's column in WHERE without expecting it to undo the LEFT JOIN
WHERE e.salary > 90000 after a LEFT JOIN silently drops every row where e.salary is NULL, including all the unmatched rows the LEFT JOIN was written to keep, turning it back into something closer to an INNER JOIN. If unmatched rows need to stay, filter inside the ON clause instead, or accept that this particular filter is meant to exclude them.
Checking a non-key column for IS NULL instead of the table's primary key
WHERE e.first_name IS NULL happens to work if first_name is never genuinely NULL for a real row, but it's fragile, relying on the primary key (e.id) is the reliable way to detect "no match," since a real row's primary key is never NULL.
Best Practices
- Put the table whose every row must appear on the left, right after FROM.
- Check the right table's primary key for IS NULL when looking for unmatched left rows, not just any column.
- Be careful adding WHERE conditions on the right table after a LEFT JOIN, they can quietly cancel out the outer join's whole purpose.
Interview Questions
How do you find rows in one table that have no matching row in another, using a join?
LEFT JOIN the second table, then add WHERE second_table.id IS NULL. This keeps every row from the first table, including unmatched ones, then filters down to only the rows where nothing on the right side matched.
Why might adding a WHERE condition on the right-hand table silently break a LEFT JOIN?
LEFT JOIN fills unmatched right-side columns with NULL to keep the left row. A WHERE clause that compares a right-table column (WHERE e.salary > 90000) evaluates to unknown, not true, for those NULLs, so WHERE filters them out, which removes exactly the unmatched rows the LEFT JOIN was meant to preserve.
Summary
LEFT JOIN keeps every row from the table named right after FROM, filling NULL into any unmatched right-table columns. Combined with WHERE right_table.id IS NULL, it's the standard way to find rows with no match at all, like Beacon Robotics' empty Legal department.
What's Next?
RIGHT JOIN next, the mirror image of LEFT JOIN, plus a note on the one database in this course that doesn't support it.
Ready to Practice SQL?
Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.
Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.