Запрос SQL для поиска записей, отсутствующих в другой таблице

Быстрый ответ

Чтобы найти записи в Table1, которые не имеют соответствий в Table2, можно использовать метод LEFT JOIN или оператор NOT EXISTS.

Вот пример использования LEFT JOIN для большей читабельности кода:

SELECT t1.*
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.ID
WHERE t2.ID IS NULL;

Пример с NOT EXISTS представлен ниже. Он обеспечивает точность результатов благодаря подзапросу:

SELECT *
FROM Table1 t1
WHERE NOT EXISTS (
  SELECT 1 FROM Table2 t2 WHERE t2.ID = t1.ID
);

Выбор между этими методами зависит от ваших персональных предпочтений в кодировании и требований к производительности запроса. Оба варианта возвращают одинаковый результат — список идентификаторов из Table1, которых нет в Table2.

Обзор альтернатив и их особенностей

При работе с большими наборами данных или выполнении сложных запросов небезразлично понимать особенности разных подходов, что может стать настоящим спасением ресурсов.

Временами используется еще один метод — NOT IN, однако он имеет свои нюансы:

SELECT ID, Name FROM Table1 WHERE ID NOT IN (SELECT ID FROM Table2);

Использование NOT IN может привести к неожиданным результатам, если подзапрос вернет NULL, что может привести к неполному результату.

Также еще существует ключевое слово EXCEPT:

SELECT ID FROM Table1
EXCEPT
SELECT ID FROM Table2;

Оператор EXCEPT возвращает уникальные значения из первого запроса, которые отсутствуют во втором запросе. Но его использование требует проверки совместимости с вашей СУБД и производительности по сравнению с JOIN.

Подготовка к повышению производительности

Следите за планами выполнения ваших запросов, чтобы ускорить их работу. Так, в PostgreSQL оператор NOT EXISTS может быть более предпочтителен, чем NOT IN, особенно если подзапросы могут включать NULL, а объемы данных велики. В некоторых случаях замена EXCEPT на JOIN может увеличить производительность.

SELECT *
FROM Table1 t1
WHERE NOT EXISTS (
  SELECT TOP 1 NULL FROM Table2 t2 WHERE t2.ID = t1.ID
);

В подзапросе использование SELECT TOP 1 NULL может улучшить производительность NOT EXISTS, поскольку оптимизатор останавливается после нахождения первого совпадения.

Практические примеры использования

Эти SQL-шаблоны применяются в различных ситуациях:

Поиск клиентов без заказов

Для определения клиентов, не совершивших заказы:

SELECT CustomerID, CustomerName 
FROM Customers c 
WHERE NOT EXISTS (
  SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID
);

Синхронизация между системами

Для выявления контактов CRM, которых нет в списке рассылки:

SELECT CRM_ID 
FROM CRM_Contacts 
LEFT JOIN MailingList ON CRM_Contacts.CRM_ID = MailingList.ContactID 
WHERE MailingList.ContactID IS NULL;

Удаление записей-сирот

Для поддержки целостности базы данных можно удалять записи, у которых отсутствуют соответствующие внешние ключи:

SELECT *
FROM ChildTable
WHERE NOT EXISTS (
  SELECT 1 FROM ParentTable WHERE ParentTable.ID = ChildTable.ParentID
);

Визуализация

Представив себе библиотеки с разным набором книг, можно лучше понять данный принцип:

Библиотека A (📚): [ID книги 123, ID книги 456, ID книги 789]
Библиотека B (🏛): [ID книги 456, ID книги 789, ID книги 101]

Цель — найти те книги Библиотеки A, которых нет в Библиотеке B.

Результат таков:

В Библиотеке A, но не в Библиотеке B: 📖 [ID книги 123]

Отображаются уникальные ID из первого массива, которые отсутствуют во втором.

Особенности работы

Стратегии сравнения данных чувствительны к NULL и распределению данных, при работе с ними следует быть аккуратным:

Обработка NULL

Значения NULL могут привести к неточным результатам при использовании NOT IN. NOT EXISTS и LEFT JOIN...IS NULL избегают этой проблемы и являются более предпочтительными.

Распределение данных

Размеры данных и их расположение играют значительную роль в производительности запроса. Важно учитывать индексы, уникальность и вероятность повторения идентификаторов при оптимизации запросов.

Полезные материалы

1. SQL JOIN и разные типы JOIN – Stack Overflow — подробный обзор работы с JOIN в SQL.
2. SQL – EXCEPT – Tutorialspoint — обширное описание оператора EXCEPT.
3. Десять типичных ошибок при проектировании баз данных – Simple Talk — полезное руководство по избеганию распространенных ошибок и оптимизации SQL-запросов.
4. Улучшение производительности SQL-запросов через избегание функций в WHERE-условии – MSSQLTips — советы по повышению эффективности SQL-запросов.