Left Join Predicate Placement and the NOT IN NULL Trap
In the HR reporting schema, EMPLOYEES holds 107 rows (employee_id, last_name, salary, department_id, manager_id) and DEPARTMENTS holds 27 rows (department_id, department_name). Five departments currently have no employees, and three employees have a NULL department_id. A report developer writes:
SELECT d.department_name, e.last_name FROM departments d LEFT JOIN employees e ON d.department_id = e.department_id WHERE e.salary > 8000 ORDER BY d.department_name;
The result has 19 rows and only 14 distinct department names; the business expected every department to appear, with blanks where nobody earns more than 8000. The same developer then writes a second query listing employees whose department is not among the departments managed by manager 100:
SELECT last_name FROM employees WHERE department_id NOT IN (SELECT department_id FROM employees WHERE manager_id = 100);
The subquery returns 6 rows, one of which has a NULL department_id, and the outer query returns zero rows. Explain both symptoms and produce correct SQL.
1. Explain why the LEFT OUTER JOIN returns 19 rows instead of the expected department list, and rewrite the query so every department appears with its qualifying employees.
Read the worked answer
What happens. A LEFT OUTER JOIN is logically evaluated as (1) join, (2) null-extend the preserved side, (3) apply WHERE. For a department with no qualifying employee, step 2 produces a row whose e.salary is NULL. WHERE e.salary > 8000 evaluates to UNKNOWN for that row, and UNKNOWN is not TRUE, so the row is discarded. The outer join silently degrades to an inner join: the 19 rows are just the employees earning more than 8000, and departments without such employees vanish.
Rule. A filter on the optional (right) table belongs in the ON clause. A filter on the preserved (left) table may stay in WHERE.
SELECT d.department_name, e.last_name
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
AND e.salary > 8000
ORDER BY d.department_name, e.last_name;
If the report must show exactly 27 rows, aggregate instead of listing names:
SELECT d.department_name,
COUNT(e.employee_id) AS high_earners
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
AND e.salary > 8000
GROUP BY d.department_name;
COUNT(e.employee_id) returns 0 for an empty department; COUNT(*) would wrongly return 1 because the null-extended row still counts.
2. Explain why the NOT IN query returns zero rows, and give two rewrites that return the intended employees.
Read the worked answer
Why zero rows. x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. The last comparison is UNKNOWN, so the conjunction can never be TRUE for any value of x. Because one of the six subquery rows has a NULL department_id, the predicate is UNKNOWN for every employee. (If the subquery returned no rows at all, NOT IN would return every row - the opposite extreme, and worth remembering.)
Rewrite 1 - NOT EXISTS (NULL-safe, keeps NULL-department employees):
SELECT e.last_name
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM employees m
WHERE m.manager_id = 100
AND m.department_id = e.department_id);
For an employee whose department_id is NULL, the inner comparison is UNKNOWN, no row is found, so NOT EXISTS is TRUE and the employee is returned. That matches the English meaning: NULL is not among the listed departments.
Rewrite 2 - keep NOT IN but make the list NULL-free:
SELECT last_name
FROM employees
WHERE department_id NOT IN (
SELECT department_id
FROM employees
WHERE manager_id = 100
AND department_id IS NOT NULL);
This is not identical: it also drops the three employees whose own department_id is NULL, because NULL NOT IN (...) is UNKNOWN. If the business wants those three listed, use NOT EXISTS.
Third option: LEFT JOIN ... ON ... WHERE m.employee_id IS NULL is equivalent to NOT EXISTS but is easy to break by adding another predicate later.
3. A colleague keeps the LEFT JOIN but moves the salary test into ON and then adds `WHERE e.manager_id = 100`. Predict the row count and explain the general placement rule.
Read the worked answer
Prediction: the result collapses back to employee-level rows. WHERE e.manager_id = 100 evaluates to UNKNOWN on every null-extended row, so departments without a manager-100 employee disappear exactly as before. The join syntax did not change; only the position of the filter did.
SELECT d.department_name, e.last_name
FROM departments d
LEFT JOIN employees e
ON d.department_id = e.department_id
AND e.manager_id = 100
ORDER BY d.department_name, e.last_name;
Decision rule.
- Predicate on the nullable side: put it in ON. In WHERE it is applied after null extension and removes the preserved rows.
- Predicate on the preserved side (for example
d.department_name LIKE 'S%'): WHERE is correct and often better, because it reduces the preserved set before the join. - If a WHERE filter on the nullable side is genuinely intended, then an inner join expresses the intent more honestly, and the optimizer sees the same thing anyway.
Related trap. Mixing comma-style joins with ANSI joins, or putting the outer-join (+) operator on the wrong side, produces the same class of silent row loss. Pick one style and apply the nullable-side filter in the ON clause consistently.