SQL Ready | Базы Данных on Telegram: “Двойной NOT EXISTS: реляционное деление без подс…”

Двойной 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 | #практика