Найти департаменты без сотрудников с помощью SQL

Что проверяют

Знаете ли вы, как искать отсутствие связи, и понимаете ли разницу между способами это сделать. Задача короткая, но у неё три рабочих решения с разным поведением на пустых значениях, и интервьюер обычно просит их сравнить.

Как отвечать

Дайте основной вариант и сразу проговорите, почему он работает.

SELECT d.department_id, d.department_name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.department_id
WHERE e.employee_id IS NULL;

Левое соединение сохраняет все департаменты, а для тех, к кому сотрудник не подобрался, столбцы из employees заполняются пустыми значениями. Условие проверяется по столбцу, который в employees пустым не бывает, то есть по первичному ключу.

Второй вариант читается лучше и обычно предпочтительнее:

SELECT d.department_id, d.department_name
FROM departments d
WHERE NOT EXISTS (
  SELECT 1 FROM employees e WHERE e.department_id = d.department_id
);

Он выражает намерение прямо: нужны департаменты, для которых в employees нет ни одной строки. Планировщик разворачивает такой запрос в антисоединение, то есть по скорости это тот же план, что у первого варианта.

Третий вариант, через группировку с условием на счётчик, тоже даст верный ответ, но заставит считать группы там, где достаточно проверить факт существования.

Уточните, надо ли учитывать уволенных: если в таблице есть признак активности, департамент с одними уволенными обычно тоже считается пустым.

Где спотыкаются

Берут NOT IN с подзапросом. Если среди сотрудников найдётся хоть одна строка с пустым department_id, сравнение даст неопределённость, и запрос вернёт ноль строк. Дефект тихий: на тестовых данных всё сходится, на боевых выдача пустая.

Ставят условие на столбец из employees, который сам допускает пустое значение. Тогда в выдачу попадут департаменты, где сотрудники есть, просто у них не заполнено проверяемое поле.

Переносят условие из WHERE в ON. При левом соединении это меняет смысл: строки не отфильтруются, они просто перестанут совпадать, и в ответ уйдут все департаменты подряд.