Выборка строк из MySQL, отсутствующих после LEFT JOIN

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

Если вам предстоит отобрать строки из таблицы Table1, которые не находят соответствие в таблице Table2, произведите следующий запрос:

-- Когда требуется точность и скорость
SELECT Table1.*
FROM Table1
LEFT JOIN Table2 ON Table1.id = Table2.fk_id
WHERE Table2.fk_id IS NULL;

С помощью LEFT JOIN для каждой строки из Table1 происходит попытка сопоставления данных в Table2. Если соответствующая строка не найдена, то такие строки связываются с NULL – это и становится в дальнейшем условием отбора в операторе WHERE.

Проясняем детали: приемы с псевдонимами и множественными условиями

Сэкономим время и символы: использование псевдонимов

Псевдонимы делают текст запроса более лаконичным и удобным для чтения:

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

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

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

Объединение условий: когда строка должна удовлетворять нескольким ключам

В случае использования составных ключей, JOIN может учитывать сразу несколько полей:

SELECT t1.*
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 
ON t1.id = t2.fk_id AND t1.another_id = t2.another_fk_id
WHERE t2.fk_id IS NULL;

Здесь происходит сопоставление данных по двум параметрам – id и another_id. А затем, условие t2.fk_id IS NULL фильтрует строки без соответствий.

Запросы с использованием IN для композитных ключей

Вот как можно найти записи из Table1, которые не связаны ни с одним из пользователей t2.user_one и t2.user_two:

SELECT t1.*
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t1.id IN (t2.user_one, t2.user_two)
WHERE t2.user_one IS NULL AND t2.user_two IS NULL;

Получается что-то подобное ситуации, когда никто из друзей не пришел на вашу вечеринку, и приходится развлекаться в одиночку.

Оптимизация запросов с помощью EXISTS

EXISTS помогает оптимизировать SQL, упрощая процесс выборки нужных строк:

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

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

Профессиональное обращение с NULL

Особенности NULL в объединениях

В SQL NULL подобен вертолёту, меняя свои свойства в зависимости от контекста. Он может появиться как результат ненайденной пары при JOIN, так и как значение поля. Эту особенность стоит учесть при составлении запросов.

Работа с несколькими LEFT JOIN: разделение запроса на части

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

SELECT t1.*
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t1.id = t2.user_one
LEFT JOIN Table3 AS t3 ON t1.id = t3.user_two
WHERE t2.user_one IS NULL AND t3.user_two IS NULL;

Разделение запроса на отдельные логические блоки сделает его проще в отладке и понимании.

Обходные пути: исключение совпадающих строк

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

SELECT t1.*
FROM Table1 AS t1
LEFT JOIN Table2 AS t2 ON t1.id = t2.fk_id
WHERE t2.fk_id IS NULL AND t1.some_column NOT IN (<some_values>);

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

Продвинутые сценарии: использование подзапросов

Подзапросы – универсальный инструмент

Многоуровневые логические запросы стоит упрощать с помощью подзапросов:

SELECT t1.*
FROM Table1 AS t1
LEFT JOIN (SELECT id FROM Table2 WHERE some_condition) AS t2 ON t1.id = t2.id
WHERE t2.id IS NULL;

Тут подзапросы функционируют как способ приведения сложных выражений к компактной форме.

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

Рассмотрим концепцию LEFT JOIN с NULL, представив её на примере коробок с карандашами:

Коробка А (🖍): [Карандаш 1, Карандаш 2, Карандаш 3]
Коробка В (🖍): [Карандаш 2, Карандаш 3, Карандаш 4]

LEFT JOIN выведет нам карандаши из коробки А, отсутствующие в коробке В:

🖍👈: [Карандаш 1 (⬛), Карандаш 2, Карандаш 3]
# Карандаши из Коробки А соотнесены с пространством из Коробки В, где ⬛ обозначает отсутствие пары.

Для того, чтобы найти одинокие карандаши из Коробки А, сделайте запрос:

SELECT * FROM `Коробка А` AS a
LEFT JOIN `Коробка В` AS b ON a.crayon = b.crayon
WHERE b.crayon IS NULL;

Результат: 🖍1. Карандаш из Коробки А, не найденный в Коробке В.

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

1. MySQL :: Руководство по MySQL 8.0 :: 15.2.13.2 JOIN Clause — Официальное руководство по синтаксису JOIN в MySQL.
2. Свежие вопросы с тегами 'mysql+left-join+null' на Stack Overflow — Активное онлайн-сообщество, обсуждение и ответы на вопросы о LEFT JOIN и NULL.
3. Понятие LEFT JOIN в SQL на W3Schools — Статья, объясняющая концепцию LEFT JOIN, подходит как новичкам в SQL, так и опытным специалистам.
4. SQL | Join (Inner, Left, Right и Full Joins) на GeekforGeeks — Подробный разбор всех типов объединений, расширяет возможности использования SQL.
5. SQL LEFT JOIN на Mode — Практическое руководство по использованию LEFT JOIN и работы с NULL.
6. Visual JOIN — Интерактивный материал, позволяющий визуально ознакомиться с работой JOIN в SQL. Для тех, кто предпочитает визуальное обучение текстовому.