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

😎 Очень интересная статья на Хабре: «Как устроено шардирование PG в процессинге Яндекс Такси»!

В этой статье:
• Узнаете, зачем крупным системам переходить от одной базы к распределённому хранению данных;
• Разберётесь, как работает выбор шарда и какие архитектурные решения помогают масштабировать PostgreSQL;
• Посмотрите на инженерные подходы из высоконагруженного продакшена.

🔊 Продолжай читать на Habr!

➡️ SQL Ready | #статья

📂 Шпаргалка по числовым функциям!

Например, ROUND() используется для округления значений, MOD() — для получения остатка от деления, а агрегатные функции AVG(), SUM(), MIN() и MAX() помогают выполнять вычисления по наборам данных.

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

Сохрани, чтобы не потерять!

➡️ SQL Ready | #ресурс

🐱 PostgreSQL Course RU — курс по PostgreSQL и SQL для разработчиков!

В репозитории собраны материалы для изучения работы с базами данных: от основ SQL и проектирования таблиц до функций, индексов, транзакций, оконных функций, PL/pgSQL и продвинутых возможностей PostgreSQL. Отличный вариант, чтобы разобраться с базами данных и перейти от простых запросов к пониманию работы СУБД.

Оставляю ссылочку: GitHub 📱

➡️ SQL Ready | #репозиторий

😍 SQL-Tutorial — бесплатный учебник по SQL с практическими заданиями!

Онлайн-учебник для изучения SQL: от базовых запросов до более сложной работы с данными. Теория сопровождается примерами и упражнениями, поэтому изученные конструкции можно сразу закреплять на практике. Материал разделён на главы, что удобно для поэтапного обучения.

📌 Оставляю ссылочку: sql-tutorial.ru

➡️ SQL Ready | #ресурс

Настройте оценку пользовательских функций!

Для пользовательской функции PostgreSQL позволяет явно указать стоимость вызова через COST, а для функции, возвращающей набор строк, — ожидаемое число строк через ROWS.

Причём пересоздавать функцию не обязательно:

ALTER FUNCTION find_orders(bigint)
ROWS 5;

ALTER FUNCTION expensive_check(jsonb)
COST 500;

Чем выше COST, тем дороже планировщик считает вычисление и тем сильнее старается не выполнять его лишний раз.

PostgreSQL прямо предоставляет COST и ROWS как информацию для оптимизации пользовательских функций.

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.id, o.*
FROM users u
CROSS JOIN LATERAL find_orders(u.id) o;

🔥 Если пользовательская функция ломает оценки строк или вызывается слишком часто, проверьте COST и ROWS — иногда правильный план получается без переписывания запроса и новых индексов.

➡️ SQL Ready | #совет

Проверяйте JSON без преобразования!

Если JSON приходит как text, необязательно делать ::jsonb и ловить ошибку преобразования. IS JSON просто вернёт true или false.

SELECT '{"id": 42}' IS JSON;     -- true
SELECT '{broken}'  IS JSON;     -- false

Можно сразу потребовать конкретный тип JSON:

SELECT payload IS JSON OBJECT
FROM staging;

SELECT payload IS JSON ARRAY
FROM staging;

Есть и более интересная проверка — запрет повторяющихся ключей:

SELECT '{"id":1,"id":2}'
       IS JSON OBJECT WITH UNIQUE KEYS;
-- false

Это можно использовать непосредственно в ограничении таблицы:

CREATE TABLE incoming_events (
    payload text NOT NULL
        CHECK (payload IS JSON OBJECT WITH UNIQUE KEYS)
);

🔥 IS JSON позволяет валидировать JSON как обычное условие SQL — без преобразований, исключений и регулярных выражений.

➡️ SQL Ready | #совет