Join avanzate, semi-join, anti-join e logica insiemistica

Un report clienti che raddoppia il fatturato dopo una join, un controllo anti-frode che perde utenti senza match, un catalogo che risulta completo finché qualcuno non conta a mano: quasi mai è colpa del database. È confusione tra tre operazioni diverse — rappresentare una relazione, filtrare righe in base a un’altra tabella, sottrarre un insieme da un altro — scritte con una sintassi che sembra quasi identica. Questa pagina mette in ordine le join avanzate, i semi-join, gli anti-join e gli operatori insiemistici, con il criterio per scegliere l’operazione giusta prima di scriverla e i controlli per accorgersi quando il risultato mente in silenzio. Siamo sul binario ml-tabellare: il lavoro è tutto su tabelle, chiavi e insiemi di righe, senza modelli.

L’idea in una frase

In una frase: questa lezione insegna a scegliere tra JOIN che moltiplicano, semi-join che filtrano e operazioni di insieme, per tenere il grano del risultato sotto controllo.

Il metodo in cinque passi

1. Scrivi in una frase la domanda con verbi di esistenza o assenza prima di scegliere l’operatore di combinazione.
2. Dichiara il grano atteso e conta le righe prima e dopo la JOIN per intercettare duplicazioni silenziose.
3. Filtra l’esistenza con EXISTS e l’assenza con NOT EXISTS senza cambiare grano né aggiungere colonne.
4. Risolvi stati vigenti al momento dell’evento con JOIN laterale ordinata e limite unitario.
5. Quadra complementari e totali e verifica unicità delle chiavi e NULL di correlazione prima di pubblicare.

Perché la domanda giusta vale più della sintassi giusta

Il SQL di base insegna le join come un meccanismo: due tabelle, una condizione di uguaglianza, un risultato. Il SQL analitico ribalta il problema: il meccanismo lo conosci, quello che manca è capire quale domanda stai ponendo. “Quali ordini hanno un cliente associato” e “quali clienti hanno fatto almeno un ordine” sembrano la stessa domanda, ma producono insiemi diversi, con cardinalità diversa, e solo una delle due risposte è quella che serve al report.

Conviene fissare tre concetti prima di toccare la tastiera. Il grain è l’unità di ogni riga del risultato: una riga per ordine, una per cliente, una per cliente-giorno. La cardinalità della relazione dice quante righe di B possono corrispondere a una riga di A: uno-a-uno, uno-a-molti, molti-a-molti. La direzionalità dice da che parte si guarda: tenere tutte le righe di A e agganciare B dove esiste, oppure tenere solo l’intersezione.

Da qui discende una regola pratica: ogni operazione di combinazione risponde a una domanda precisa. La tabella sotto è la mappa da tenere a mente per tutto il resto della pagina.

Operazione	Domanda a cui risponde	Cosa restituisce
INNER JOIN	Quali righe compaiono in entrambi gli insiemi?	Solo l’intersezione, con colonne di entrambe
LEFT JOIN	Quali righe di A hanno o non hanno corrispondenza in B?	Tutte le righe di A, NULL dove B manca
Semi-join (EXISTS)	Quali righe di A hanno almeno un match in B?	Solo righe di A, mai duplicate
Anti-join (NOT EXISTS)	Quali righe di A non hanno alcun match in B?	Solo righe di A senza corrispondenza
LATERAL	Per ogni riga di A, cosa calcola una subquery che dipende da quella riga?	Una riga di A espansa con il calcolo
As-of join	Qual era lo stato di B al momento dell’evento in A?	Il match temporale più vicino non posteriore
UNION ALL / UNION	Quali righe stanno in A più quelle in B?	Le righe accodate, con o senza deduplica
EXCEPT	Quali righe di A non sono in B?	La differenza insiemistica
INTERSECT	Quali righe sono identiche in A e B?	L’intersezione insiemistica

La colonna decisiva è la seconda. Se non sai dire in una frase quale domanda stai ponendo, qualsiasi sintassi scegli è una scommessa.

Inner, left e la trappola della duplicazione silenziosa

Il difetto più costoso delle join è che non falliscono mai rumorosamente. Una condizione di join su chiave non univoca non solleva errori: moltiplica le righe e gonfia ogni aggregazione a valle. Prendi il caso classico, revenue media per ristorante attivo: con una INNER JOIN tra ristoranti e ordini, ogni ristorante compare tante volte quanti sono i suoi ordini, e la media calcolata sulle righe duplicate non è più la media per ristorante.

-- Sbagliato: ogni ristorante pesa per il numero di ordini
-- La media è calcolata sulle righe duplicate, non sui ristoranti
SELECT AVG(r.fatturato) AS media_fatturato
FROM ristoranti AS r
JOIN ordini AS o ON o.ristorante_id = r.id
WHERE o.data_ordine >= CURRENT_DATE - INTERVAL '90 days';

Il controllo che smaschera il problema è banale e quasi nessuno lo fa: confrontare il conteggio prima e dopo la join. Se la tabella di sinistra ha 1.200 ristoranti e il risultato della join ne ha 48.000 righe, la join ha cambiato il grain e ogni AVG, COUNT o SUM va ricalcolato sul grain giusto, tipicamente pre-aggregando prima di unire.

-- Corretto: prima aggrego gli ordini per ristorante, poi unisco
-- Il grain resta una riga per ristorante
WITH ordini_per_ristorante AS (
  SELECT
    ristorante_id,
    COUNT(*) AS n_ordini
  FROM ordini
  WHERE data_ordine >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY ristorante_id
)
SELECT AVG(r.fatturato) AS media_fatturato
FROM ristoranti AS r
JOIN ordini_per_ristorante AS o ON o.ristorante_id = r.id;

La LEFT JOIN aggiunge un secondo rischio: i NULL nelle colonne di destra. Filtrare con WHERE b.colonna = qualcosa dopo una LEFT JOIN trasforma silenziosamente la outer join in una inner join, perché il confronto con NULL è falso e le righe senza match spariscono. La condizione sulla tabella di destra va nella clausola ON, non nel WHERE. In alternativa, il filtro va scritto tenendo conto dei NULL con OR b.colonna IS NULL. La distinzione vale la pena interiorizzarla: la ON decide come le tabelle si agganciano, il WHERE decide quali righe del risultato sopravvivono.

Semi-join con EXISTS: filtrare senza moltiplicare

Il semi-join risponde a una domanda di esistenza: quali righe di A hanno almeno un corrispondente in B. Non aggiunge colonne di B, non duplica righe di A, non cambia il grain. In SQL si scrive con EXISTS e una subquery correlata, ed è l’operatore giusto ogni volta che il predicato dice “almeno uno”: clienti con almeno un ordine sportivo, ristoranti attivi negli ultimi 90 giorni, utenti che hanno aperto almeno una email della campagna.

-- Utenti con almeno un ordine nella categoria 'Sport'
-- Ogni utente compare una sola volta, anche con 50 ordini sportivi
SELECT u.nome, u.email
FROM utenti AS u
WHERE EXISTS (
  SELECT 1
  FROM ordini AS o
  WHERE o.utente_id = u.id
    AND o.categoria = 'Sport'
)
ORDER BY u.nome;

La variante ingenua — JOIN seguita da GROUP BY o DISTINCT per ricompattare — restituisce lo stesso insieme ma paga due costi inutili. Materializza le righe duplicate per poi buttarle via e rende la query fragile: chi la modifica tra sei mesi deve capire che il DISTINCT non è decorativo ma strutturale. Con EXISTS l’intento è leggibile nella sintassi e l’ottimizzatore può fermarsi al primo match per ogni riga (semi-join con short-circuit), senza scandire tutta la tabella interna.

C’è un’alternativa che si vede spesso, WHERE id IN (SELECT ...), e vale la pena sapere quando evitarla. Se la subquery restituisce NULL, IN ha semantica a tre valori e il risultato può svuotarsi in modi sorprendenti, mentre EXISTS ragiona solo in termini booleani: la riga esiste o non esiste. Per i semi-join, EXISTS con subquery correlata è quasi sempre la forma più sicura.

Sul piano delle performance, il semi-join scala con il numero di righe di A che superano il test, non con la cardinalità del match. Cento ordini per utente costano quanto uno: al primo trovato, la ricerca si ferma. È il motivo per cui i filtri di esistenza su tabelle di eventi voluminose dovrebbero essere semi-join quasi per default.

Anti-join con NOT EXISTS: trovare chi manca

L’anti-join è il gemello del semi-join: quali righe di A non hanno alcun corrispondente in B. Titoli mai visti in dodici mesi, utenti che non hanno mai comprato elettronica, fatture senza pagamento associato. Si scrive con NOT EXISTS, stessa struttura, polarità opposta.

-- Utenti che non hanno mai ordinato nella categoria 'Elettronica'
SELECT u.nome, u.email
FROM utenti AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM ordini AS o
  WHERE o.utente_id = u.id
    AND o.categoria = 'Elettronica'
)
ORDER BY u.nome;

Anche qui esiste la variante con LEFT JOIN ... WHERE o.id IS NULL, e anche qui la forma con NOT EXISTS è preferibile per due motivi. Il primo è semantico: la LEFT JOIN materializza l’intero prodotto esterno per poi scartare quasi tutto, mentre l’anti-join logico scarta appena trova un match. Il secondo è la leggibilità dell’intento: NOT EXISTS dice “tenere gli assenti”, la LEFT JOIN con filtro su NULL dice la stessa cosa in modo obliquo, e obliquo significa che il prossimo manutentore potrebbe “semplificarla” rompendola.

L’avvertenza sui NULL qui è decisiva e riguarda la terza forma, NOT IN. Se la colonna confrontata contiene anche un solo NULL, NOT IN restituisce zero righe: il confronto x NOT IN (1, 2, NULL) non è mai vero, perché il NULL rende il predicato sconosciuto per ogni riga. È uno dei bug SQL più classici e più difficili da notare, perché la query gira senza errori e restituisce un insieme vuoto che sembra un dato (“nessun utente inattivo, ottimo”) invece di un guasto logico. Regola pratica: per gli anti-join usare NOT EXISTS, mai NOT IN su colonne nullable.

Un controllo economico prima di mettere un anti-join in produzione: contare separatamente i due insiemi. Se gli utenti totali sono 200.000, quelli con almeno un ordine elettronico sono 190.000 e l’anti-join ne restituisce 3.000 invece di 10.000, qualcosa non torna — tipicamente un filtro temporale applicato da una sola parte o un NULL nella chiave di correlazione.

Lateral join e as-of join: quando la relazione dipende dalla riga

Fin qui la condizione di join era statica: uguaglianza tra chiavi. La LATERAL join serve quando la parte destra deve essere calcolata per ogni riga della sinistra, con parametri presi dalla riga stessa. Il caso tipico è una funzione che stima qualcosa — ETA di una corsa, punteggio di rischio di una transazione, i tre ordini più recenti di un cliente — e che non può essere espressa come join su chiavi perché il suo input è la riga corrente.

-- Per ogni richiesta di corsa, calcola ETA con i dati di quella riga
-- La subquery laterale vede r.pickup_lat, r.hour, r.traffic_level
SELECT
  r.request_id,
  eta.minuti_previsti,
  eta.intervallo_confidenza
FROM richieste_corse AS r
CROSS JOIN LATERAL (
  SELECT minuti_previsti, intervallo_confidenza
  FROM stima_eta(r.pickup_lat, r.pickup_lon, r.ora, r.livello_traffico)
) AS eta
WHERE r.data_richiesta = CURRENT_DATE;

Senza LATERAL, la stessa logica richiederebbe di precalcolare la funzione su tutte le combinazioni possibili e poi unire — spesso impraticabile. Due dettagli contano: con CROSS JOIN LATERAL le righe di sinistra senza risultato dalla subquery spariscono, con LEFT JOIN LATERAL ... ON true sopravvivono con NULL, e la scelta segue la stessa logica delle join ordinarie. E la subquery laterale gira una volta per riga: su milioni di righe il costo va stimato, eventualmente limitando prima le righe di sinistra con un filtro selettivo.

L’as-of join risolve un problema diverso e frequentissimo in analisi temporali: agganciare a ogni evento lo stato di una dimensione al momento dell’evento. Il prezzo del listino vigente quando l’ordine è stato fatto, il piano di abbonamento attivo quando l’utente ha guardato il video, il tasso di cambio del giorno della transazione. Una join su uguaglianza fallisce perché i timestamp non coincidono quasi mai; serve il record di B più vicino senza superare il timestamp di A.

-- Per ogni ordine, il prezzo di listino vigente a quella data
-- Il match è: stesso prodotto, data listino <= data ordine, la più recente
SELECT o.ordine_id, o.data_ordine, l.prezzo AS prezzo_vigente
FROM ordini AS o
JOIN LATERAL (
  SELECT prezzo
  FROM listini AS l
  WHERE l.prodotto_id = o.prodotto_id
    AND l.data_validita <= o.data_ordine
  ORDER BY l.data_validita DESC
  LIMIT 1
) AS l ON true;

I database con supporto nativo (ASOF JOIN in ClickHouse, kdb+, DuckDB con ASOF) esprimono lo stesso concetto con sintassi dedicata e implementazioni ottimizzate su dati ordinati. Il punto concettuale non cambia: quando la relazione è “lo stato vigente al momento”, né l’uguaglianza né l’intervallo fisso bastano, serve un match temporale ordinato. E il controllo di qualità specifico è verificare gli eventi anteriori al primo record di B: ordini più vecchi del listino più vecchio restano senza match, e decidere se è corretto (dato mancante vero) o un buco da colmare con un valore di default è una scelta analitica, non tecnica.

UNION, EXCEPT, INTERSECT: ragionare per insiemi

Gli operatori insiemistici combinano i risultati di due query invece delle tabelle, riga per riga, e richiedono stessa struttura: stesso numero di colonne, tipi compatibili nella stessa posizione. Sembrano primitivi, ma risolvono con eleganza problemi per cui le join sono lo strumento sbagliato: consolidare cataloghi da più sistemi, trovare record presenti qui e assenti là, verificare che due pipeline producano lo stesso insieme.

UNION ALL accoda le righe mantenendo i duplicati, UNION accoda e deduplica. La distinzione ha conseguenze su correttezza e costo: se i duplicati tra le sorgenti sono impossibili per costruzione (partizioni disgiunte per data o regione), UNION ALL è corretto e più economico perché evita l’ordinamento per deduplica. Se i duplicati sono possibili — tre sistemi che descrivono gli stessi listing, come nel caso sotto — la scelta tra UNION e UNION ALL decide se il catalogo finale conta ogni listing una volta o tre.

-- Listing presenti in tutti e tre i sistemi (intersezione)
SELECT listing_id FROM db_host
INTERSECT
SELECT listing_id FROM sistema_qualita
INTERSECT
SELECT listing_id FROM motore_prezzi;

-- Listing dell'host mai passati dal controllo qualità (differenza)
SELECT listing_id FROM db_host
EXCEPT
SELECT listing_id FROM sistema_qualita;

-- Catalogo unificato con provenienza: qui i duplicati informano,
-- quindi UNION ALL con colonna sorgente invece di UNION
SELECT listing_id, 'host' AS sorgente FROM db_host
UNION ALL
SELECT listing_id, 'qualita' AS sorgente FROM sistema_qualita
UNION ALL
SELECT listing_id, 'prezzi' AS sorgente FROM motore_prezzi;

Tre avvertenze pratiche. Primo, EXCEPT e INTERSECT confrontano l’intera riga, non una chiave: due righe con stesso listing_id ma attributi diversi sono righe diverse e l’intersezione le esclude entrambe. Quando serve confrontare solo le chiavi, proiettare solo la chiave prima dell’operatore, come negli esempi sopra. Secondo, NULL nei confronti insiemistici: in EXCEPT e INTERSECT due NULL nella stessa posizione sono considerati uguali (diversamente dal WHERE, dove NULL = NULL è sconosciuto), un dettaglio che cambia i conteggi quando le chiavi sono nullable. Terzo, l’ordinamento: ORDER BY va una sola volta in fondo e si riferisce alle colonne del risultato combinato, non alle singole query — e nelle query componenti va evitato perché nella maggior parte dei dialetti è sintatticamente vietato o semanticamente inutile.

Un uso sottovalutato di EXCEPT è come test di regressione tra pipeline: SELECT ... FROM nuova_pipeline EXCEPT SELECT ... FROM vecchia_pipeline restituisce esattamente le righe che la nuova versione aggiunge o perde. Eseguito a ogni deploy su un campione congelato, è il modo più economico per accorgersi che un refactoring ha cambiato i dati oltre al codice.

NULL, cardinalità e controlli prima di fidarsi del risultato

Quasi tutti i bug silenziosi delle combinazioni tra tabelle hanno due radici: chiavi duplicate dove se ne presumeva l’unicità, e NULL dove si presumeva un valore. Entrambi si controllano con query da poche righe che conviene eseguire prima dell’analisi vera, non dopo aver presentato il numero sbagliato.

Il controllo di cardinalità verifica che la chiave di JOIN sia davvero univoca dal lato in cui la si presume tale. Prima di unire gli eventi alla tabella clienti su cliente_id, una GROUP BY cliente_id HAVING COUNT(*) > 1 sulla tabella clienti dice se esistono duplicati. Se ne esistono, la JOIN moltiplicherà le righe degli eventi per quei clienti e ogni metrica a valle sarà gonfiata esattamente in proporzione. Lo stesso vale per le chiavi composte: l’unicità va verificata sulla combinazione completa usata nella ON, non sulle singole colonne.

Il controllo sui NULL riguarda le chiavi di correlazione: una riga con chiave NULL non matcha mai in una JOIN su uguaglianza, né in EXISTS, e sparisce dal risultato senza lasciare traccia. Contare i NULL nelle colonne di JOIN di entrambe le tabelle — e decidere esplicitamente se vanno esclusi, imputati o segnalati — chiude una delle falle più comuni. A questi due controlli si aggiunge la quadratura: la somma delle parti deve tornare con il totale. Se gli utenti attivi più gli inattivi (semi-join più anti-join complementari) non danno il totale utenti, c’è un terzo insieme nascosto, quasi sempre righe con chiave NULL o filtri temporali applicati in modo asimmetrico.

Anche i fusi orari meritano una riga: confrontare timestamp con timezone diverse in un as-of join o in un filtro sugli ultimi 90 giorni sposta i confini delle finestre di qualche ora, abbastanza per far sparire o apparire eventi al bordo. Normalizzare a UTC prima di confrontare costa una riga e previene una classe intera di discrepanze irreproducibili.

Come scegliere l’operazione in produzione

A questo punto il criterio di scelta si può enunciare come procedura. Primo passo: scrivere in una frase la domanda, usando i verbi che corrispondono alle operazioni — “tenere gli ordini con cliente” (inner), “tenere tutti i clienti con i loro ordini dove esistono” (left), “tenere i clienti con almeno un ordine” (semi-join), “tenere i clienti senza ordini” (anti-join), “per ogni ordine, il prezzo vigente allora” (as-of), “accodare due insiemi” (union). Se la frase non viene naturale, il requisito è ambiguo e va chiarito con chi ha chiesto l’analisi prima di scrivere SQL.

Secondo passo: dichiarare il grano atteso del risultato e verificarlo contando le righe prima e dopo. Terzo: scegliere la forma sintattica più esplicita tra quelle equivalenti — EXISTS invece di JOIN più deduplica, NOT EXISTS invece di NOT IN su colonne nullable, filtro sulla tabella esterna nella ON invece che nel WHERE. Quarto: eseguire i controlli di cardinalità, NULL e quadratura, e fissare baseline e soglie (periodo precedente, segmento comparabile) così che una rottura futura dei dati si veda dal numero prima che dal reclamo.

Resta il trade-off onesto: semi-join e anti-join con subquery correlate sono ottimizzati bene dai motori moderni, ma su volumi molto grandi e chiavi ad alta cardinalità la forma con pre-aggregazione o con JOIN esplicita su insieme deduplicato può vincere. L’unico arbitro affidabile è il piano di esecuzione misurato sui propri dati, non la regola generale. La regola generale serve a scrivere la query corretta, il piano di esecuzione serve a renderla veloce. In quest’ordine.

[SQL]Prova tu

Usa un semi-join (EXISTS) per trovare gli utenti che hanno fatto almeno un ordine nella categoria 'Sport'. Mostra nome e email.

SELECT u.nome, u.email FROM utenti u JOIN ordini o ON u.id = o.utente_id WHERE o.categoria = 'Sport' GROUP BY u.nome, u.email

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Usa un anti-join (NOT EXISTS) per trovare gli utenti che NON hanno mai fatto un ordine nella categoria 'Elettronica'. Ordina per nome.

SELECT u.nome, u.email FROM utenti u LEFT JOIN ordini o ON u.id = o.utente_id AND o.categoria = 'Elettronica' WHERE o.id IS NULL

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

Riferimenti: Stonebraker, M. e Rowe, L. (1986). «The Design of POSTGRES». Proceedings of ACM SIGMOD, pp. 340–355. Chamberlin, D. D. (1998). A Complete Guide to DB2 Universal Database. Morgan Kaufmann, capitolo 8 su subquery e tabelle derivate. Celko, J. (2014). Joe Celko’s SQL for Smarties: Advanced SQL Programming, quinta edizione. Morgan Kaufmann, capitolo 21 sulle operazioni insiemistiche.

Verdetto: usa EXISTS per esistenza e NOT EXISTS per assenza su chiavi nullable, LEFT JOIN con condizione in ON per tenere i senza match, laterale con LIMIT 1 per stati vigenti e UNION ALL con sorgente quando i duplicati informano.

Un esempio che fa testo: il disegno di POSTGRES

Per chiudere con un riferimento fondativo: Michael Stonebraker e Lawrence Rowe hanno descritto il disegno di POSTGRES negli atti SIGMOD del 1986 alle pagine da 340 a 355. Il lavoro fissa la semantica di join, subquery correlate e viste che ancora guidano EXISTS e NOT EXISTS nei motori moderni. Con 200000 utenti totali e 190000 acquirenti di elettronica, un anti join corretto restituisce 10000 assenti mentre un NOT IN su colonna nullable restituisce zero righe. Senza controllo di cardinalità e quadratura dei complementari, la stessa combinazione gonfia medie e svuota insiemi senza errori di sintassi.

Domande per chiudere la lezione

1. Quando una JOIN su chiave non univoca gonfia medie e conteggi senza sollevare errori?
2. Perché NOT IN su colonna nullable restituisce insieme vuoto mentre NOT EXISTS trova gli assenti?
3. Quando sposti un filtro sulla tabella esterna da WHERE a ON per non trasformare una LEFT JOIN in interna?
4. Quale quadratura fai tra attivi, inattivi e totale utenti prima di fidarti di semi-join e anti-join?