SQL‑запрос для получения работ без привязанных материалов

Условие

В задаче дано две связанные таблицы — works и materials. Нужно составить запрос, который вернёт только те записи из works, для которых в таблице materials нет ни одной строки.

CREATE TABLE works
(
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE materials
(
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    quantity INTEGER,
    work_id BIGINT,
    FOREIGN KEY (work_id) REFERENCES works(id) ON DELETE CASCADE
);

INSERT INTO works(id, name)
VALUES (1, 'work 1'),
       (2, 'work 2'),
       (3, 'work 3');

INSERT INTO materials(id, name, quantity, work_id)
VALUES (1, 'mat 1', 10, 1),
       (2, 'mat 2', 20, 2),
       (3, 'mat 2', 15, 2),
       (4, 'mat 4', 30, 2);

Требуется написать запрос, который при выполнении над указанными данными вернёт только work 3, поскольку у него нет связанных записей в materials.

Подсказка: подойдёт конструкция LEFT JOIN … ON … WHERE materials.id IS NULL либо NOT EXISTS/NOT IN.

Ответ

Используйте NOT EXISTS с проверкой материалов по work_id. Он возвращает работы, для которых не найдено ни одной связанной строки, и не страдает от NULL в несвязанных материалах.

SELECT w.id, w.name
FROM works AS w
WHERE NOT EXISTS (
    SELECT 1
    FROM materials AS m
    WHERE m.work_id = w.id
)
ORDER BY w.id;

На указанных данных вернётся work 3. Индекс materials(work_id) полезен для такой проверки. Вариант NOT IN без фильтрации NULL опасен: один NULL внутри подзапроса может исключить ожидаемые результаты из-за трёхзначной логики SQL.