Теория: ANTI JOIN (левое соединение с NULL)

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

В этом уроке мы научимся решать такие задачи с помощью LEFT JOIN и RIGHT JOIN. Прием, который мы будем использовать, часто называют ANTI JOIN, но это именно название приема, а не инструкция SQL.

Используем ANTI JOIN

Рассмотрим пример. Пусть база данных хранит информацию о покупателях и заказах.

Таблица customers хранит информацию о покупателях:

customers

Таблица orders хранит информацию о заказах, которые делали покупатели:

orders

View on DB Fiddle

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

Сначала построим просто соединение двух таблиц:

SELECT
    cus.customer_name,
    ord.order_id
FROM customers AS cus
LEFT JOIN orders AS ord ON
    cus.customer_id = ord.customer_id

Результат выполнения запроса:

View on DB Fiddle

Столбец order_id — это первичный ключ таблицы orders. Поэтому в результате запроса там может быть значение NULL, если у клиента нет ни одного заказа. Эту особенность мы можем использовать для фильтрации с помощью оператора WHERE:

SELECT cus.customer_name -- убрали столбец order_id, он все равно не нужен
FROM customers AS cus
LEFT JOIN orders AS ord
    ON
        cus.customer_id = ord.customer_id
WHERE ord.order_id IS NULL -- отбираем только покупателей без заказа

Результат выполнения запроса:

View on DB Fiddle

После фильтрации мы получили список клиентов, которые не сделали ни одного заказа.

Сравниваем LEFT ANTI JOIN и RIGHT ANTI JOIN

Выше мы рассмотрели как реализовать ANTI JOIN с помощью LEFT JOIN. Схематично это можно представить так:

На схеме видно, что в результат попали только те строки из первой (левой) таблицы, которые не удалось связать ни с одной из строк второй (правой) таблицы.

Также прием ANTI JOIN можно реализовать и с помощью RIGHT JOIN:

В этом случае мы получим все те строки из второй (правой) таблицы, которые не были связаны со строками из первой (левой) таблицы.

Выводы

Цель ANTI JOIN — отобрать строки, у которых нет пары при соединении таблиц. Этот прием реализуется с помощью левого или правого внешнего соединения и фильтрации. Для фильтрации мы используем столбец, который не может принимать значение NULL, например, первичный ключ. Так мы можем однозначно отличить строки без пары.