Выборка строк из PostgreSQL, отсутствующих в другой таблице

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

Для извлечения данных из table1, которые не встречаются в table2, лучше всего использовать конструкцию NOT EXISTS с подзапросом в контексте table2. Таким образом, мы можем эффективно сопоставлять наши записи в table1:

SELECT *
FROM table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM table2
    WHERE table2.id = t1.id
);

Этот метод изящно отсеивает записи из table1, для которых в table2 нет соответствий по полю id.

Расширенный запрос: Альтернативы и распространенные ошибки

Метод с LEFT JOIN / IS NULL

Применение LEFT JOIN / IS NULL является эффективным, если СУБД хорошо оптимизирует join-операции:

SELECT t1.*
FROM table1 t1
LEFT JOIN table2 t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

Подробнее об этом расскажет наш спикер на видео

EXCEPT и ALL: Мощное сочетание

Если нужны только уникальные строки, EXCEPT может стать отличным решением:

SELECT id 
FROM table1
EXCEPT
SELECT id 
FROM table2;

Запомните, что EXCEPT по умолчанию удаляет дубликаты. Для сравнения с учётом повторений используйте ALL.

NOT IN – применяйте с осмотрительностью

Оператор NOT IN может казаться привлекательным своей простотой, однако с ним связаны определенные трудности. Например, наличие NULL значений в table2.id приводит к некорректным результатам, и данная конструкция может быть неэффективна при масштабировании базы данных:

SELECT *
FROM table1
WHERE id NOT IN (SELECT id FROM table2);

Дьявол в мелочах: Оптимизация запросов

Поиск общих свойств

Анализируя данные в различных таблицах, всегда сопоставляйте колонки с аналогичным значением или содержимым.

Уточнение условий в WHERE

Ключом к эффективным результатам запросов является грамотно составленный предикат в WHERE. Четкое определение условий обеспечивает большую вероятность оптимизации запроса.

Оптимизируем SELECT

Составляя SELECT, следуйте правилу включения только необходимых для анализа колонок. Это снижает нагрузку на сервер и ускоряет обработку запросов.

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

Представим, что table1 (🏘️🅰️) содержит дома наших друзей, а table2 (🏘️🅱️) — дома незнакомцев. Мы хотим послать приглашения (💌) только нашим друзьям.

🏘️🅰️: [Дом 1, Дом 2, Дом 3]
🏘️🅱️: [Дом 2, Дом 3, Дом 4]

SELECT НомерДома FROM 🏘️🅰️
EXCEPT
SELECT НомерДома FROM 🏘️🅱️;

Результат будет следующим:

💌🚚: [Дом 1]

Здесь 💌🚚 означает дома, получившие от нас приглашения. В результате только Дом 1 из table1 (🏘️🅰️) не совпадает с table2 (🏘️🅱️), и именно он получает приглашение.

Практическое применение

Рассмотрим пример поиска IP-адресов, которые зарегистрированы в login_log, но отсутствуют в ip_location:

SELECT ip
FROM login_log
WHERE NOT EXISTS (
    SELECT 1
    FROM ip_location
    WHERE ip_location.ip = login_log.ip
);

Это помогает идентифицировать незарегистрированные IP для дальнейшего анализа с точки зрения безопасности, или для очистки данных.

Логика и отладка

Синтаксические ошибки и неоптимальность запросов могут проявиться в самый неподходящий момент. Используйте встроенные инструменты СУБД, например, EXPLAIN plans, чтобы выявить проблемы производительности.

Дополнительные фильтры для точности

Иногда, добавляя дополнительные фильтры, можно повысить точность выборки данных:

SELECT ip
FROM login_log
WHERE NOT EXISTS (
   SELECT 1
   FROM ip_location
   WHERE ip_location.ip = login_log.ip
)
AND login_log.date >= '2023-01-01';

Добавление колонки date позволяет фильтровать данные за конкретный период времени.

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

1. SQL LEFT JOIN Keyword — подробное изучение SQL JOIN, одной из ключевых особенностей работы с реляционными базами данных.
2. Select rows which are not present in other table – Stack Overflow — практический опыт коллег и обсуждение вопроса выборки строк, отсутствующих в другой таблице SQL.
3. Different Ways to Compare SQL Server Tables Schema and Data — статья об опциях сравнения схем и данных таблиц в SQL Server, помогающая выявить отсутствующие данные.
4. PostgreSQL: Documentation: Joins Between Tables — полезный ресурс из официальной документации PostgreSQL, рассказывающий о соединении таблиц и методах выделения уникальных записей.