After this lesson you can list every row of a table even when it has no related rows, find the rows that have no match, and avoid the usual mistakes that quietly turn a left join back into an inner join.
This is where the two odd rows from the sample data earn their place. Employee 18, Ravi, has no department, and department 6, Research, has no employees. An inner join loses both. An outer join keeps them.
LEFT JOIN: keep everything on the left
A LEFT JOIN returns every row from the table written before the word JOIN (the left table). For each left row it looks for matches in the right table. If it finds some, you get the matched pairs, as in an inner join. If it finds none, you still get the left row, with NULL in every column that would have come from the right table.
Start with employees on the left and departments on the right, looking at the last few employees:
SELECT e.id, e.first_name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE e.id >= 17
ORDER BY e.id;+----+------------+-------------+
| id | first_name | department |
+----+------------+-------------+
| 17 | Sana | Support |
| 18 | Ravi | NULL |
| 19 | Diya | Engineering |
| 20 | Aman | Sales |
+----+------------+-------------+Ravi (18) is there, with NULL for the department name. The matching inner join would have lost him.
Flip it around: departments on the left
Now put departments on the left. Research appears with NULL employee columns:
SELECT d.id, d.name AS department, e.first_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE d.id IN (5, 6)
ORDER BY d.id, e.first_name;+----+------------+------------+
| id | department | first_name |
+----+------------+------------+
| 5 | Support | Karan |
| 5 | Support | Pooja |
| 5 | Support | Sana |
| 6 | Research | NULL |
+----+------------+------------+Support (5) has three employees, so it appears three times. Research (6) appears once, with a NULL first name. The left table decides which rows must be kept, so the order of the tables matters.
Finding rows that have no match
The NULL-filled rows are useful. They let you ask "which departments have nobody in them?" Join, then keep only the rows where a right-side column is NULL:
SELECT d.id, d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;+----+----------+
| id | name |
+----+----------+
| 6 | Research |
+----+----------+Test a column that can never be NULL for a real match, such as the primary key e.id. If you tested a column that may legitimately be NULL, you could not tell a missing match from a real row with an empty value. And remember to write IS NULL, because = NULL is never true.
Employees with no department work the same way, with the tables the other way round:
SELECT e.id, e.first_name, e.job_title
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;+----+------------+------------+
| id | first_name | job_title |
+----+------------+------------+
| 18 | Ravi | Consultant |
+----+------------+------------+Counting with a left join
A left join is the right tool for "count per group, including groups with zero". But there is a trap. COUNT(*) counts rows, and the NULL-filled row for Research is still a row:
SELECT d.name AS department,
COUNT(*) AS wrong_count,
COUNT(e.id) AS employees
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.name
ORDER BY d.name;+-------------+-------------+-----------+
| department | wrong_count | employees |
+-------------+-------------+-----------+
| Engineering | 6 | 6 |
| Finance | 2 | 2 |
| Marketing | 3 | 3 |
| Research | 1 | 0 |
| Sales | 5 | 5 |
| Support | 3 | 3 |
+-------------+-------------+-----------+For Research, COUNT(*) says 1 and COUNT(e.id) says 0. The second one is the true answer, because COUNT(column) skips NULL values. When you count the many side of a left join, count one of its columns, not *.
The WHERE versus ON trap
Suppose you want every department listed, but only high earners (above 100000) shown next to them. It is tempting to filter in WHERE:
SELECT d.name AS department, e.first_name, e.salary
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.salary > 100000
ORDER BY d.name;+-------------+------------+--------+
| department | first_name | salary |
+-------------+------------+--------+
| Engineering | Meera | 180000 |
| Engineering | Arjun | 125000 |
| Engineering | Kavya | 125000 |
| Finance | Lata | 115000 |
| Marketing | Dev | 110000 |
| Sales | Vikram | 150000 |
+-------------+------------+--------+Departments with no high earner vanish. For them, e.salary is NULL and the test NULL > 100000 is not true, so WHERE removes the row. The query has behaved like an inner join. Move the test into ON, where it only controls which right-hand rows count as matches:
SELECT d.name AS department, e.first_name, e.salary
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id AND e.salary > 100000
ORDER BY d.name, e.first_name;+-------------+------------+--------+
| department | first_name | salary |
+-------------+------------+--------+
| Engineering | Arjun | 125000 |
| Engineering | Kavya | 125000 |
| Engineering | Meera | 180000 |
| Finance | Lata | 115000 |
| Marketing | Dev | 110000 |
| Research | NULL | NULL |
| Sales | Vikram | 150000 |
| Support | NULL | NULL |
+-------------+------------+--------+Now every department is present. Research and Support have no one above 100000, so they show NULL instead of vanishing, while the other departments list their high earners. The rule of thumb: conditions on the left table can go in WHERE; conditions on the right table usually belong in ON, unless you deliberately want to drop the unmatched rows, as when you test IS NULL.
RIGHT JOIN
A RIGHT JOIN is the mirror image: it keeps every row of the right table. These two queries return the same rows:
SELECT d.name AS department, e.first_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id
WHERE d.id = 6;+------------+------------+
| department | first_name |
+------------+------------+
| Research | NULL |
+------------+------------+SELECT d.name AS department, e.first_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE d.id = 6;+------------+------------+
| department | first_name |
+------------+------------+
| Research | NULL |
+------------+------------+Because you can always swap the table order, most people write only LEFT JOIN, and read queries from top to bottom with the "main" table first. Choose whichever makes the query easier to read, and stay consistent.
Differences between databases
LEFT JOIN is the short form of LEFT OUTER JOIN, and OUTER is optional everywhere. RIGHT JOIN works in MySQL, PostgreSQL and SQL Server, and in SQLite since version 3.39. Older SQLite versions only have LEFT JOIN, so swap the tables there. For MySQL's treatment of these joins, see LEFT JOIN and RIGHT JOIN in MySQL.
Common mistakes
- Filtering the right table in WHERE. This removes the
NULLrows and silently turns the query into an inner join. Put the test inON. - Using
COUNT(*)for counts that should be zero. UseCOUNT(right_table.column). - Testing for no match with
= NULL. UseIS NULL. - Testing a nullable column to find no match. Test the right table's primary key.
- Putting the tables in the wrong order. The table you want to keep in full must be on the left of
LEFT JOIN.
Exercise
List every project together with the number of people assigned to it and the total hours, showing zero if nobody is assigned. Then list the employees who are not assigned to any project at all, with their job titles.
Show one possible answer
SELECT p.name AS project, COUNT(a.employee_id) AS people, COALESCE(SUM(a.hours), 0) AS total_hours
FROM projects p
LEFT JOIN assignments a ON a.project_id = p.id
GROUP BY p.id, p.name
ORDER BY p.name;
SELECT e.first_name, e.last_name, e.job_title
FROM employees e
LEFT JOIN assignments a ON a.employee_id = e.id
WHERE a.employee_id IS NULL
ORDER BY e.id;+-------------------+--------+-------------+
| project | people | total_hours |
+-------------------+--------+-------------+
| Billing Migration | 3 | 390 |
| Customer Portal | 4 | 1160 |
| Mobile App | 4 | 1100 |
| Q3 Sales Push | 5 | 1050 |
| Spring Campaign | 3 | 480 |
+-------------------+--------+-------------+
+------------+-----------+----------------------+
| first_name | last_name | job_title |
+------------+-----------+----------------------+
| Meera | Iyer | Engineering Director |
| Pooja | Reddy | Support Lead |
| Karan | Mehta | Support Engineer |
| Sana | Qureshi | Support Engineer |
| Ravi | Pillai | Consultant |
+------------+-----------+----------------------+In this data every project has at least one person, so the zero case does not show up, but the query is written to handle it. Five employees have no assignment.
Key points
- LEFT JOIN keeps every row from the left table. Where the right table has no match, its columns are filled with NULL.
- To find rows with no match, left join and then filter on a right-side column that is NULL, such as WHERE e.id IS NULL.
- COUNT(*) counts the NULL-filled row as one; COUNT(e.id) counts only real matches. Use the column form to get 0.
- A filter on the right table belongs in ON, not WHERE, if you want to keep the unmatched left rows.
- RIGHT JOIN is a LEFT JOIN with the two tables swapped. Most people simply write LEFT JOIN.