OUTER JOIN: trovare i "buchi"

Nel Modulo 3 hai visto LEFT JOIN: tieni tutte le righe di sinistra, anche quelle senza match a destra (che diventano NULL). In questo capitolo ampliamo lo strumentario con RIGHT JOIN e FULL OUTER JOIN, e impariamo a usarli per trovare i "buchi" nei dati: righe in una tabella che non hanno corrispondenza nell'altra.

I tre tipi di 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 si può sempre riscrivere come B LEFT JOIN A con la stessa ON: è una questione di leggibilità. Molti stili (incluso questo corso) preferiscono usare sempre LEFT JOIN per coerenza.

Pattern "anti-join": trovare i mancanti

L'uso più comune di LEFT JOIN non è "arricchire con i dati a destra", ma trovare le righe di sinistra che NON hanno match a destra. Si chiama 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;

Logica del pattern:

1. LEFT JOIN accosta a ogni prodotto le sue eventuali recensioni;
2. per i prodotti senza recensioni, tutte le colonne di r sono NULL;
3. WHERE r.id IS NULL tiene solo quei prodotti.

Confronto chiave: una id IS NULL su una colonna NOT NULL della tabella di destra significa "non c'è stato match". Spesso si filtra sulla PK della tabella di destra proprio per questo.

FULL OUTER JOIN

FULL OUTER JOIN tiene entrambi i lati senza match. Si usa raramente, tipicamente per controlli di integrità dati:

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;

Le righe con c.id IS NULL sarebbero ordini orfani (un customer_id che non esiste in customers). Quelle con o.id IS NULL sono clienti senza ordini. Sul nostro dataset le foreign key garantiscono che non esistano ordini orfani; rimangono solo i clienti senza ordini.

Prova tu

Esercizio di ripasso