Why can NOT IN return no rows, and what should you use instead?

x NOT IN (a, b, NULL) expands to x <> a AND x <> b AND x <> NULL. The last term is UNKNOWN, so the whole condition is never TRUE, and WHERE returns no rows. One NULL in the list silently empties the result.

-- returns nothing if any employee has manager_id NULL
SELECT name FROM employees
WHERE id NOT IN (SELECT manager_id FROM employees);

This is common, because columns like manager_id are nullable and the CEO has none.

Two safe alternatives:

-- NOT EXISTS: NULL-safe, usually the best
SELECT e.name FROM employees e
WHERE NOT EXISTS (
  SELECT 1 FROM employees m WHERE m.manager_id = e.id
);

-- LEFT JOIN anti join
SELECT e.name FROM employees e
LEFT JOIN employees m ON m.manager_id = e.id
WHERE m.id IS NULL;

You can also filter the subquery with WHERE manager_id IS NOT NULL. The planners of most engines turn NOT EXISTS and the join into an efficient anti join.

Prefer NOT EXISTS whenever the subquery column can be NULL.