Lesson 18 / 37 · Joins

Rows with no match: the anti-join

LEFT JOIN, then keep only the rows where the right side stayed NULL. IS NULL is NULL-safe, so this is the right way to ask "which departments have nobody?".

The query

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

Try IS NOT NULL: the opposite question returns 5 rows, Engineering and Sales twice, once per employee.

Next: Joins chain left to right, one step each · All lessons