SQL Ready | Базы Данных
Двойной NOT EXISTS: реляционное деление без подсчёта строк!
Задачи вида «найти сущности, удовлетворяющие всем условиям из некоторого набора» встречаются регулярно: все разрешения роли, все обязательные атрибуты товара, все зависимости конфигурации или все компетенции проекта. В реляционной алгебре такой класс задач связан с операцией деления.
Предположим, требования проекта и навыки сотрудников представлены отношениями:
project_requirements(project_id, skill_id)
employee_skills(employee_id, skill_id)
Требуется получить сотрудников, обладающих всеми навыками проекта 42. Один из распространённых вариантов — подсчитать совпадения:
SELECT e.id
FROM employees e
JOIN employee_skills s
ON s.employee_id = e.id
JOIN project_requirements r
ON r.project_id = 42
AND r.skill_id = s.skill_id
GROUP BY e.id
HAVING COUNT(DISTINCT r.skill_id) = (
SELECT COUNT(DISTINCT skill_id)
FROM project_requirements
WHERE project_id = 42
);
Запрос корректен, если skill_id не допускает NULL. DISTINCT необходим, если уникальность пар (project_id, skill_id) и (employee_id, skill_id) не гарантирована схемой.
Но условие «сотрудник имеет все требуемые навыки» можно выразить напрямую: не должно существовать требования, для которого у сотрудника нет соответствующего навыка:
SELECT e.id
FROM employees e
WHERE NOT EXISTS (
SELECT 1
FROM project_requirements r
WHERE r.project_id = 42
AND NOT EXISTS (
SELECT 1
FROM employee_skills s
WHERE s.employee_id = e.id
AND s.skill_id = r.skill_id
)
);
Внешний NOT EXISTS ищет отсутствие невыполненных требований, а внутренний проверяет отсутствие соответствующего навыка. Важное свойство — дубли не влияют на результат:
INSERT INTO employee_skills (employee_id, skill_id)
VALUES
(7, 10),
(7, 10),
(7, 20);
Если проект требует навыки 10 и 20, сотрудник 7 удовлетворяет требованиям независимо от числа повторений (7, 10). EXISTS проверяет наличие строки, а не их количество.
На практике такие дубли лучше запрещать ограничениями:
ALTER TABLE project_requirements
ADD CONSTRAINT uq_project_requirement
UNIQUE (project_id, skill_id);
ALTER TABLE employee_skills
ADD CONSTRAINT uq_employee_skill
UNIQUE (employee_id, skill_id);
Также skill_id в такой модели обычно следует объявлять NOT NULL. Иначе COUNT(DISTINCT skill_id) игнорирует NULL, а сравнение s.skill_id = r.skill_id с NULL не даст совпадения, что может привести к различию результатов двух подходов.
Если у проекта 42 вообще нет требований, двойной NOT EXISTS вернёт всех сотрудников: нет ни одного требования, которое сотрудник не выполняет. Если по правилам предметной области проект без требований не должен возвращать кандидатов, это нужно указать отдельно:
SELECT e.id
FROM employees e
WHERE EXISTS (
SELECT 1
FROM project_requirements r
WHERE r.project_id = 42
)
AND NOT EXISTS (
SELECT 1
FROM project_requirements r
WHERE r.project_id = 42
AND NOT EXISTS (
SELECT 1
FROM employee_skills s
WHERE s.employee_id = e.id
AND s.skill_id = r.skill_id
)
);
Двойной NOT EXISTS полезен тем, что выражает исходную задачу напрямую: вместо подсчёта совпадений мы проверяем отсутствие хотя бы одного невыполненного требования.
🔥 Для условий вида «выполнены все требования», «присутствуют все зависимости» или «есть соответствие каждому элементу набора» это одна из наиболее естественных форм реляционного деления в SQL.
➡️ SQL Ready | #практика
30 · 2.2K ·