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 є не "збагачення даними на праворуч", але знайдіть рядки ліворуч, які НЕ збігаються з правильно. Це називається anti-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 правої таблиці означає "збігу не було". Ви часто фільтруєте PK правої руки стіл саме з цієї причини.

ПОВНЕ ЗОВНІШНЄ З'ЄДНАННЯ

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, є клієнтами без замовлень. У нашому наборі даних зовнішні ключі гарантують відсутність сиріт замовлення існують; залишаються тільки клієнти без замовлень.

Спробуйте самі

Повторна вправа