Lesson 17 / 37 · Joins

A WHERE on the right table turns LEFT JOIN into INNER JOIN

The LEFT JOIN keeps Research with NULL salary. Then WHERE tests e.salary > 80000: NULL is not TRUE, so Research is dropped after all. dequery flags this with "watch out".

The query

SELECT d.name, e.name AS employee, e.salary
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.salary > 80000

Move the condition into ON, ON e.dept_id = d.id AND e.salary > 80000, and drop the WHERE: all four departments stay, Support and Research with NULLs.

Next: Rows with no match: the anti-join · All lessons