Подзапросы и проверки целостности
Ищем отсутствие связей без ловушки NOT IN с NULL и отделяем допустимый ноль от нарушения договора.
- Выражать отсутствие связи через NOT EXISTS.
- Находить контрпример для NOT IN с NULL.
- Отличать отсутствие дочерней записи от нарушения внешнего ключа.
Отсутствие тоже бывает разным
Задача без комментариев не является ошибкой схемы: договор разрешает ноль комментариев. Задача с автором, которого нет в users, нарушает внешний ключ. Пользователь без назначений — обычный участник команды, а не обязательно забытая запись. Прежде чем писать запрос поиска аномалий, назовите инвариант и допустимые исключения. Иначе полезный отчёт превратится в список нормальных состояний, которые никто не обязан исправлять.
В нашем снимке нет комментариев у t2, t4 и t6. Чтобы найти их, для каждой строки tasks проверяем существование дочернего комментария с тем же task_id. NOT EXISTS формулирует вопрос прямо: нет ни одной подходящей строки. Выбор SELECT 1 внутри выражения не извлекает полезную колонку для внешнего отчёта; важен факт существования. Корреляция c.task_id=t.id связывает внутренний поиск с текущей задачей. Если забыть эту связь, условие станет общим для всех задач и вернёт неправильный набор.
Q8 · Задачи без комментариев
SELECT t.id FROM tasks t
WHERE NOT EXISTS (
SELECT 1 FROM comments c WHERE c.task_id=t.id
)
ORDER BY t.id;
-- t2
-- t4
-- t6Ловушка NOT IN
Нужно найти пользователей, которым не назначена ни одна задача. Список assignee_id содержит u2, NULL, NULL, u1, u3, u1. Кажется естественным написать users.id NOT IN (SELECT assignee_id FROM tasks). Но наличие NULL меняет логическую оценку такого отрицания; наш неправильный запрос не вернёт ни одного пользователя, хотя u4 не имеет назначений. На данных, где все задачи назначены, этот дефект мог бы не проявиться.
NOT EXISTS с корреляцией по t.assignee_id=u.id даёт точный ответ u4. Другой допустимый вариант — исключить NULL внутри NOT IN, но правило нужно явно выразить, а не надеяться, что будущие записи всегда будут заполнены. Выбор конструкции связан с вопросом: ищем отсутствие равного идентификатора, а не отсутствие неизвестного значения. Для ревью полезно рядом хранить минимальный контрпример с NULL, чтобы объяснение не зависело от большого набора строк.
Q9 · Пользователи без назначений
SELECT u.id FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM tasks t WHERE t.assignee_id=u.id
)
ORDER BY u.id;
-- u4Нулевая выборка как ожидаемый результат
Q13 ищет задачи с отсутствующим автором через tasks LEFT JOIN users и WHERE u.id IS NULL. Ожидается пустой набор, поскольку fixture создан с включёнными внешними ключами и корректными ссылками. Запрос с нулём строк здесь означает успех проверки инварианта. Для отчёта зафиксируйте само условие и снимок: фраза «ничего не найдено» без определения поиска не проверяема.
Не выключайте ограничения в рабочей базе ради искусственной ошибки. Контрпример можно создать в отдельной временной таблице или копии, которая не является исходным снимком. В этом курсе проверка отказа неверного assignee_id выполняется внутри отдельного savepoint и полностью откатывается. Сравнение снимков до и после доказывает отсутствие изменений именно у учебного соединения. Оно не проверяет параллельные транзакции, репликацию или права реальной СУБД.
Различайте техническую целостность и бизнес-правило. Внешний ключ подтверждает существование пользователя, но не его активность, команду или разрешение стать исполнителем. В SQLite-CORE-1 таких полей нет, поэтому вывод о соблюдении командных прав из Q13 был бы выдумкой. Хороший QA-отчёт показывает не только найденные строки, но и предел того, что вообще можно узнать из выбранной схемы.
Источники и проверка фактов
SQLite · IN, NOT IN and EXISTSТаблица поведения NULL и существование подзапроса.SQLite · Foreign keysСмысл ссылочной целостности и её включение.Авторский учебный материал · Редакция QA Orbit · версия 2026-10-28.1