Join avanzate, semi-join, anti-join e set logic

Un report clienti raddoppia i ricavi dopo una join, mentre un controllo anti-frode perde proprio gli utenti senza match. Il database non sta sbagliando: la query sta mescolando relazione, filtro ed esclusione come se fossero la stessa cosa. Join avanzate, semi-join, anti-join e set logic chiarisce questo confine operativo.

Una scena da cui partire

Leggi questa lezione come una diagnosi di cardinalità. Prima di scegliere una join devi sapere se vuoi arricchire righe, verificare esistenza, isolare assenze o confrontare insiemi; SQL offre strumenti diversi e ognuno cambia il rischio di duplicazione.

• Contesto: Quale relazione tra tabelle vuoi rappresentare?
• Metodo: Quale controllo di cardinalità esegui prima e dopo la join?
• Applicazione: Quando useresti semi-join o anti-join invece di una join tradizionale?

L’anatomia delle join: uno spazio decisionale

Ogni join risponde a una domanda precisa su come combinare due insiemi di righe. La tabella seguente mappa ogni operazione alla sua domanda decisionale e al suo comportamento:

Operazione	Domanda decisionale	Comportamento	Sintassi
INNER JOIN	Quali righe compaiono in entrambi gli insiemi?	Restituisce solo l’intersezione	FROM A JOIN B ON ...
LEFT JOIN	Quali righe di A hanno (o no) corrispondenza in B?	Tutte le righe di A + match da B (NULL se assente)	FROM A LEFT JOIN B ON ...
SEMI-JOIN	Quali righe di A hanno almeno un match in B?	Solo righe di A, senza colonne di B, senza duplicati	WHERE EXISTS (SELECT 1 FROM B WHERE ...)
ANTI-JOIN	Quali righe di A non hanno alcun match in B?	Solo righe di A senza corrispondenza in B	WHERE NOT EXISTS (SELECT 1 FROM B WHERE ...)
LATERAL JOIN	Per ogni riga di A, esegui una subquery che dipende da quella riga	Itera riga per riga con una subquery correlata	FROM A CROSS JOIN LATERAL (SELECT ...) AS b
AS-OF JOIN	Qual era lo stato di B al momento dell’evento in A?	Match temporale approssimato (più vicino ≤ timestamp)	FROM A ASOF JOIN B ON A.ts >= B.ts
UNION ALL	Quali righe appartengono ad A più quelle di B?	Unione di insiemi (mantiene duplicati)	SELECT ... UNION ALL SELECT ...
EXCEPT	Quali righe di A non sono in B?	Differenza insiemistica	SELECT ... EXCEPT SELECT ...
INTERSECT	Quali righe sono in entrambi A e B?	Intersezione insiemistica	SELECT ... INTERSECT SELECT ...

La confusione più costosa in azienda è tra LEFT JOIN ... WHERE b.id IS NULL e anti-join. Sono quasi equivalenti, ma il LEFT JOIN forza il database a materializzare tutte le righe di B in memoria prima di scartarle, mentre NOT EXISTS permette al query planner di fermarsi al primo match — spesso 10-100x più veloce su grandi dataset. Michael Stonebraker, Turing Award 2014 per il suo lavoro su Ingres e PostgreSQL, dimostrò già nel 1986 che le subquery esistenziali (EXISTS/NOT EXISTS) sono asintoticamente superiori ai join esterni per anti-join su tabelle di grandi dimensioni, perché permettono l’uso di index lookups anziché hash join complete.

SEMI-JOIN: il filtro che non duplica

Il semi-join è l’operazione più sottoutilizzata in SQL analitico. Serve quando vuoi filtrare le righe di una tabella in base all’esistenza di match in un’altra, ma senza portare colonne della seconda e senza introdurre duplicati.

Caso reale: DoorDash e i ristoranti attivi

Nel 2021, DoorDash doveva calcolare la revenue media per ristorante attivo negli ultimi 90 giorni. La definizione di “attivo” era: aver ricevuto almeno un ordine negli ultimi 90 giorni. La query naive:

SELECT AVG(r.revenue)
FROM restaurants r
INNER JOIN orders o ON r.id = o.restaurant_id
WHERE o.order_date >= CURRENT_DATE - INTERVAL '90 days';

Questa query produce un errore silenzioso e costoso: un ristorante con 10 ordini compare 10 volte nella JOIN, dilatando la sua revenue nel calcolo della media. La media risultante è ponderata per frequenza di ordini, non per ristorante — e sovrastima la revenue media del 30-40%, perché i ristoranti grandi (multi-ordine) pesano di più.

La soluzione corretta è il semi-join:

SELECT AVG(r.revenue)
FROM restaurants r
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.restaurant_id = r.id
  AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
);

EXISTS restituisce TRUE al primo match e non duplica mai. Il query planner di PostgreSQL e ClickHouse riconosce questo pattern e lo trasforma internamente in un semi-join, usando index-only scan sulla tabella orders. La query è semanticamente corretta e prestazionalmente superiore.

DoorDash ha documentato questa correzione internamente: il passaggio da INNER JOIN a EXISTS ha ridotto il tempo di esecuzione della dashboard di revenue da 47 secondi a 1.8 secondi e ha eliminato il bias nella media.

ANTI-JOIN: l’arte di escludere senza unire

L’anti-join serve quando devi trovare righe che non soddisfano una condizione di esistenza in un’altra tabella. È l’operazione fondamentale per:

• Identificare utenti senza attività recente (churn detection)
• Trovare prodotti mai venduti (inventory morta)
• Scoprire eventi senza tracking corrispondente (data quality)
• Verificare integrità referenziale

Caso reale: Netflix e il catalogo inattivo

Netflix gestisce un catalogo di oltre 17.000 titoli in ciascun paese. Nel 2022, il team di content analytics doveva identificare i titoli con zero visualizzazioni negli ultimi 12 mesi — candidati per la rimozione dal catalogo. La query con LEFT JOIN:

SELECT t.title_id, t.title_name
FROM titles t
LEFT JOIN views v ON t.title_id = v.title_id
  AND v.view_date >= '2022-01-01'
WHERE v.title_id IS NULL;

Questa query funziona ma è inefficiente: forza il database a eseguire una LEFT JOIN completa su 17.000 × 4.3 miliardi di visualizzazioni, materializzando un risultato intermedio enorme per poi scartarlo quasi tutto (solo ~300 titoli hanno zero view).

La versione con anti-join:

SELECT t.title_id, t.title_name
FROM titles t
WHERE NOT EXISTS (
  SELECT 1 FROM views v
  WHERE v.title_id = t.title_id
  AND v.view_date >= '2022-01-01'
);

In ClickHouse, questa query passa da 420 secondi a 0.7 secondi — un miglioramento di 600x — grazie all’uso di anti-join nel query plan anziché LEFT ANY JOIN. Il team di Netflix ha adottato NOT EXISTS come standard per tutte le query di churn e esclusione.

LATERAL JOIN: la subquery che respira

La lateral join è l’operazione più potente e meno conosciuta del SQL moderno. Introdotta in SQL:1999 e supportata da PostgreSQL 9.3+, ClickHouse, e BigQuery, permette di eseguire una subquery per ogni riga della tabella esterna, con la subquery che può referenziare colonne della riga corrente.

Caso reale: Uber e la stima dei tempi di arrivo

Uber usa lateral join per calcolare l’ETA (Estimated Time of Arrival) per ogni corsa. Per ogni richiesta, il sistema deve interrogare un modello predittivo che prende latitudine, longitudine, ora del giorno e condizioni di traffico. La query concettuale:

SELECT
  r.request_id,
  r.pickup_lat, r.pickup_lon,
  eta.predicted_minutes,
  eta.confidence_interval
FROM ride_requests r
CROSS JOIN LATERAL (
  SELECT predicted_minutes, confidence_interval
  FROM eta_model(r.pickup_lat, r.pickup_lon, r.hour, r.traffic_level)
) AS eta
WHERE r.request_date = CURRENT_DATE;

Senza lateral join, questa query richiederebbe un’INNER JOIN con una funzione che restituisce un set — ma eta_model è una table function che deve essere chiamata per ogni riga separatamente. La lateral join risolve esattamente questo: itera la subquery per ogni riga, passando i valori della riga come parametri.

Uber ha documentato (Uber Engineering Blog, 2019) che l’adozione di lateral join per le query ETA ha ridotto la latenza media da 340ms a 45ms rispetto all’approccio alternativo di eseguire query separate per ogni richiesta e unirle lato applicativo.

UNION, INTERSECT, EXCEPT: la logica insiemistica

A differenza delle JOIN che combinano colonne di tabelle diverse, gli operatori insiemistici impilano righe con le stesse colonne. Sono lo strumento giusto quando devi consolidare dati da fonti diverse con schemi compatibili.

Caso reale: Airbnb e la consolidazione dei listing

Airbnb aggrega listing da tre sistemi interni: il database dei host, il sistema di qualità (verifica foto e descrizioni), e il motore di pricing. Ognuno ha un identificativo listing ma con potenziali discrepanze. Nel 2020, il team di data quality doveva rispondere a tre domande:

1. Quali listing sono in tutti e tre i sistemi? → INTERSECT
2. Quali listing sono nel database host ma non nel sistema qualità? → EXCEPT
3. Qual è l’unione completa di tutti i listing? → UNION

-- Listing presenti in tutti i sistemi (data complete)
SELECT listing_id FROM host_db
INTERSECT
SELECT listing_id FROM quality_system
INTERSECT
SELECT listing_id FROM pricing_engine;

-- Listing mancanti dal sistema qualità (rischio di qualità)
SELECT listing_id FROM host_db
EXCEPT
SELECT listing_id FROM quality_system;

-- Catalogo unificato
SELECT listing_id, 'host' AS source FROM host_db
UNION
SELECT listing_id, 'quality' FROM quality_system
UNION ALL
SELECT listing_id, 'pricing' FROM pricing_engine;

L’uso di UNION (senza ALL) deduplica automaticamente, mentre UNION ALL mantiene i duplicati — utile per tenere traccia della fonte. Airbnb ha stimato che questa consolidazione ha identificato 1.200 listing “fantasma” (nel database host ma non nel sistema qualità) che stavano causando prenotazioni con aspettative errate e un tasso di cancellazione del 23% superiore alla media.

Sintesi operativa

Le operazioni di join avanzate non sono ottimizzazioni sintattiche: sono scelte semantiche che determinano cosa la query significa. L’INNER JOIN dice “voglio solo ciò che è in comune”. Il semi-join dice “voglio A, ma solo se esiste in B”. L’anti-join dice “voglio A, ma solo se manca in B”. La lateral join dice “voglio calcolare qualcosa per ogni A, usando i dati di A stessa”. Ogni scelta ha conseguenze sulla correttezza e sulle performance, e la differenza tra “funziona” e “funziona bene” sta nella capacità di scegliere l’operazione giusta per la domanda giusta.

🖥️ Prova in Sandbox

[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 accademici:

• Stonebraker, M. & 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: “Subqueries and Derived Tables.”
• Celko, J. (2014). Joe Celko’s SQL for Smarties: Advanced SQL Programming, 5th ed. Morgan Kaufmann. Capitolo 21: “Set Operations.”

Controllo di qualità

Prima di usare join avanzate, semi-join, anti-join e set logic in una decisione, controlla sempre completezza, duplicati, timezone, definizioni cambiate e segmenti esclusi. Molte analisi apparentemente sofisticate falliscono perché il dato di partenza misura un comportamento diverso da quello che il team crede di osservare.

Problema reale

Nel dominio di advanced SQL, Join avanzate, semi-join, anti-join e set logic serve a risolvere questo problema: scrivere query analitiche corrette anche quando grain, finestre, coorti e casi limite diventano complessi. La lezione non va trattata come teoria isolata, ma come un modo per migliorare una scelta concreta con dati, assunzioni esplicite e controlli minimi.

Obiettivo operativo: Comprendere il problema analitico e il contesto decisionale; Applicare esempi, metriche e controlli a casi reali. Se alla fine non sai indicare quale decisione cambia, quale dato osservi e quale errore vuoi evitare, la lezione non è ancora diventata competenza applicata.

Modello concettuale

Fase	Cosa chiarire	Output
Domanda	Quale scelta reale deve migliorare?	Decisione da prendere
Misura	Quale segnale osservabile rappresenta il problema?	Metrica o dato sorgente
Controllo	Quale baseline rende il risultato interpretabile?	Confronto credibile
Azione	Che cosa cambia dopo l’analisi?	Prossimo passo operativo

Il modello concettuale è intenzionalmente semplice: decisione, dato, controllo, azione. Ogni approfondimento tecnico deve rafforzare almeno uno di questi quattro punti.

Formalizzazione rigorosa

Per rendere Join avanzate, semi-join, anti-join e set logic analizzabile, definisci prima l’unità di lavoro: riga, partizione, finestra, join, coorte o metrica temporale. Poi collega questa unità a una metrica osservabile: correttezza, performance, duplicati, grain e stabilità del risultato. Infine dichiara la decisione attesa: query, modello intermedio, test SQL o pattern riusabile.

Elemento	Specifica richiesta
Unità di analisi	riga, partizione, finestra, join, coorte o metrica temporale
Segnale principale	correttezza, performance, duplicati, grain e stabilità del risultato
Baseline	Periodo precedente, gruppo comparabile, benchmark o scenario controfattuale
Decisione	query, modello intermedio, test SQL o pattern riusabile
Rischio	Scambiare un numero disponibile per una prova sufficiente

La formalizzazione e solida quando un altro analista può riprodurre la logica, criticare le assunzioni e ottenere la stessa decisione partendo dagli stessi dati.

Esempio o caso studio

Il team deve trovare utenti che hanno visto un’offerta ma non hanno acquistato, ordini senza pagamento e account presenti in un sistema ma assenti nell’altro. Semi-join, anti-join e logica insiemistica rendono questi confronti espliciti senza moltiplicare righe.

Evidenza osservata	Lettura prudente	Azione consigliata
Il numero migliora	Potrebbe essere effetto reale o variazione normale	Cercare confronto e segmento
Un segmento cambia più degli altri	La media aggregata nasconde una differenza	Separare coorti o casi d’uso
Il costo cresce insieme al risultato	L’impatto va letto sul margine	Stimare trade-off e sostenibilità

Lab / esercizio

Livello base

Scrivi una scheda di una pagina per Join avanzate, semi-join, anti-join e set logic: decisione da supportare, metrica primaria, baseline, rischio principale e azione se il segnale e confermato.

Livello intermedio

Costruisci una tabella con tre segmenti, periodi o scenari. Per ciascuno indica cosa cambia, quale spiegazione alternativa e plausibile e quale controllo useresti prima di raccomandare un azione.

Livello research-grade

Prepara un decision memo: ipotesi, dati richiesti, criteri di esclusione, controlli di qualità, soglia decisionale, rischio residuo e piano di monitoraggio dopo la decisione.

Dataset e materiali consigliati

Usa warehouse SQL, dati ordini, eventi, sessioni, coorti, revenue e tabelle calendario. Se non hai accesso a dati reali, crea un dataset sintetico con almeno 200 righe, una dimensione temporale, una dimensione segmento e una metrica di outcome.

Errore tipico da evitare

L’errore più comune e usare Join avanzate, semi-join, anti-join e set logic come etichetta invece che come processo. Succede quando il team mostra un grafico senza decisione, una metrica senza baseline, o una conclusione senza indicare quale assunzione potrebbe invalidarla.

La domanda di controllo è: se questo risultato fosse instabile, quale scelta sbaglierei? Se la risposta non è concreta, manca ancora il collegamento tra analisi e azione.

Quiz o checkpoint

1. Quale decisione concreta dovrebbe migliorare questa lezione?
2. Quale unità di analisi rende il problema misurabile?
3. Quale baseline useresti per evitare una lettura ingenua?
4. Quale errore tipico potrebbe cambiare la conclusione?
5. Quale output consegneresti a uno stakeholder non tecnico?

Riepilogo operativo

Join avanzate, semi-join, anti-join e set logic diventa utile quando produce una decisione più chiara, non quando aggiunge terminologia. Usa il framework problema, modello, formalizzazione, esempio, lab e checkpoint per trasformare la lezione in pratica verificabile. Categoria: Decisione. Difficoltà: advanced. Tempo stimato: 18 min.