OUTER JOIN: поиск «пробелов»

В модуле 3 вы видели LEFT JOIN: оставляйте все строки слева, даже те, которые без совпадения справа (которые становятся NULL). В этой главе мы расширите набор инструментов RIGHT JOIN и FULL OUTER JOIN, и мы научимся как их использовать, чтобы найти "дыры" в данных: строки в одной таблице, которые не имеют аналогов в другом.

Три вида OUTER JOIN

A LEFT  JOIN B  ON …  -- tutte le righe di A, con o senza match in B
A RIGHT JOIN B  ON …  -- tutte le righe di B, con o senza match in A
A FULL  JOIN B  ON …  -- tutte le righe di A *e* di B; NULL dove non c'è match

A RIGHT JOIN B всегда можно переписать как B LEFT JOIN A с тем же ON: это вопрос читабельности. Множество стилей (включая этот курс) предпочитаю всегда использовать LEFT JOIN для согласованности.

Паттерн «антисоединение»: находим недостающие

Чаще всего LEFT JOIN используется не для «обогащения данными на вправо", но найдите строки слева, которые НЕ имеют совпадений в верно. Это называется анти-соединение:

-- Prodotti senza nessuna recensione:
SELECT p.id, p.name
FROM   products p
LEFT JOIN reviews r ON r.product_id = p.id
WHERE  r.id IS NULL
ORDER BY p.id;

Логика шаблона:

1. LEFT JOIN прикрепляет отзывы о каждом товаре, если таковые имеются;
2. для товаров без отзывов все столбцы r имеют значение NULL;
3. WHERE r.id IS NULL сохраняет только эти продукты.

Проверка ключа: id IS NULL в столбце NOT NULL правой таблицы. означает "совпадения не было". Вы часто фильтруете по ПК правой руки стол именно по этой причине.

ПОЛНОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ

FULL OUTER JOIN сохраняет обе стороны без совпадений. Он используется редко, обычно для проверки целостности данных:

SELECT c.id AS customer_id, o.id AS order_id
FROM   customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id
WHERE  c.id IS NULL OR o.id IS NULL;

Строки с c.id IS NULL будут потерянными заказами (customer_id, которые не существует в customers). Те, у кого есть o.id IS NULL, являются клиентами. без заказов. В нашем наборе данных внешние ключи гарантируют, что ни один потерянный заказы существуют; остаются только клиенты без заказов.

Попробуйте сами

Обзор упражнения