SQL — найти экземпляры без привязки к изданию

Условие

Схема библиотеки: Произведение, Издание, Экземпляр(id, id_издание, …), Лог_операций. Проверить, есть ли экземпляры, не привязанные к изданию (orphan-records). По сути, контроль целостности.

Решение

Идея

«Не привязан» = либо id_издание IS NULL, либо id_издание указывает на несуществующую строку в таблице Издание. Полная проверка — два условия через LEFT JOIN.

Код

Вариант 1. NULL FK

SELECT *
FROM Экземпляр
WHERE id_издание IS NULL;

Вариант 2. FK указывает на несуществующее издание (LEFT JOIN + IS NULL)

SELECT e.*
FROM Экземпляр e
LEFT JOIN Издание i ON i.id = e.id_издание
WHERE i.id IS NULL;

Этот вариант ловит и NULL, и «битые» ссылки.

Вариант 3. NOT EXISTS — иногда быстрее на больших данных

SELECT e.*
FROM Экземпляр e
WHERE NOT EXISTS (
  SELECT 1 FROM Издание i WHERE i.id = e.id_издание
);

Сложность

• LEFT JOIN ... IS NULL называется anti-join. Современные планировщики (Postgres, Oracle) распознают его и выполняют как hash anti-join — линейно по большей таблице.
• NOT IN (SELECT ...) я бы избегал: NULL в подзапросе ломает результат (вернёт пустоту).

Подводные камни

1. NOT IN с NULL. WHERE id_издание NOT IN (SELECT id FROM Издание) — если в Издание.id есть хотя бы один NULL, результат будет пустой по логике трёхзначной логики. Используйте NOT EXISTS или LEFT JOIN.
2. Дублирование экземпляра при JOIN. Если у одного издания несколько потомков — это другая таблица, тут это безопасно. Но всегда проверяйте, что после LEFT JOIN не получили дубли.
3. «Привязан, но издание удалено». Без FK constraint в БД такое возможно — задание именно про это. С FOREIGN KEY такие orphans вообще не должны появляться.
4. Производительность. Если таблиц миллиарды — добавить индекс на Экземпляр.id_издание и Издание.id (PK уже индексирован).
5. Что отдавать заказчику. Не только COUNT, а ещё и список первых N orphan-id для расследования (например, LIMIT 100).

Эталонный ответ

SELECT e.*
FROM Экземпляр e
LEFT JOIN Издание i ON i.id = e.id_издание
WHERE i.id IS NULL;

Этот anti-join покрывает оба случая: NULL FK и FK на несуществующее издание.