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

Un report clienti che raddoppia i ricavi dopo una join, o un controllo anti-frode che perde utenti senza match, non sono errori del database. Sono segnali di confusione tra tre cose diverse: rappresentare una relazione, filtrare e escludere. Le join avanzate, i semi-join, gli anti-join e gli operatori insiemistici servono proprio a tenere distinto questo confine operativo.

Quando una query corretta dà numeri sbagliati

In SQL avanzato la difficoltà non è la sintassi in sé, ma scrivere query analitiche corrette quando grain, finestre, coorti e casi limite complicano il risultato. Il punto è scegliere l’operazione adatta alla domanda che stai facendo, perché una duplicazione o un’omissione silenziosa basta a falsare una metrica senza che nessun errore venga segnalato.

Scegliere l’operazione prima di scriverla

Prima di eseguire una join conviene rispondere a tre domande. Quale relazione tra le tabelle vuoi rappresentare? Quale controllo di cardinalità fai prima e dopo la join, per accorgerti se le righe si moltiplicano? E quando conviene un semi-join o un anti-join al posto di una join tradizionale? Ogni operazione risponde a una domanda precisa su come combinare due insiemi di righe. La tabella seguente associa ciascuna operazione alla sua domanda decisionale e al comportamento che produce:

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 ...

Rendere la logica riproducibile

Definisci l’unità di lavoro, che può essere la riga, la partizione, la finestra, il join, la coorte o una metrica temporale, e collegala a una metrica osservabile come correttezza, performance, duplicati, grain o stabilità. Poi dichiara la decisione attesa, cioè se il prodotto sarà una query, un modello, un test o un pattern riusabile, e tieni a mente il rischio principale: confondere un dato disponibile con una prova sufficiente.

Elemento	Specifica
Unità di analisi	riga, partizione, finestra, join, coorte o metrica temporale
Segnale principale	correttezza, performance, duplicati, grain, stabilità
Baseline	periodo precedente, gruppo comparabile, benchmark
Decisione	query, modello, test SQL, pattern riusabile
Rischio	interpretare un dato come prova definitiva

Quattro casi reali

Semi-join: DoorDash e i ristoranti attivi

DoorDash voleva calcolare la revenue media per ristorante attivo negli ultimi 90 giorni, definito come almeno un ordine in quel periodo. La query con INNER JOIN moltiplicava i ristoranti per il numero di ordini, sovrastimando la media. La soluzione corretta usa semi-join con EXISTS, che filtra senza duplicare:

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'
);

Questa query è semanticamente corretta e molto più efficiente.

Anti-join: Netflix e il catalogo inattivo

Netflix doveva trovare titoli senza visualizzazioni negli ultimi 12 mesi. La query con LEFT JOIN era inefficiente perché materializzava un join enorme. La versione con anti-join (NOT EXISTS) riduce drasticamente i tempi:

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'
);

Lateral join: Uber e l’ETA

Uber calcola il tempo stimato di arrivo (ETA) per ogni corsa usando una funzione che dipende dai dati di ogni riga. La lateral join consente di eseguire una subquery correlata per ogni riga:

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;

Logica insiemistica: Airbnb e i listing

Airbnb consolida listing da tre sistemi con UNION, INTERSECT e EXCEPT per identificare dati mancanti o duplicati:

-- Listing in tutti i sistemi
SELECT listing_id FROM host_db
INTERSECT
SELECT listing_id FROM quality_system
INTERSECT
SELECT listing_id FROM pricing_engine;

-- Listing mancanti nel sistema 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;

Esercitarsi sulle join

Per fissare il metodo, parti da una scheda sintetica sulle join avanzate, i semi-join, gli anti-join e la set logic, dove annoti la decisione, la metrica, la baseline, il rischio e l’azione. Poi prova a costruire una tabella con tre segmenti o scenari, indicando per ciascuno cosa cambia, quale spiegazione alternativa è plausibile e quale controllo useresti. Il passo più impegnativo è scrivere un memo decisionale con ipotesi, dati, criteri di esclusione, controlli, soglia decisionale, rischio residuo e piano di monitoraggio. In tutti i casi conviene lavorare su dati reali o sintetici con almeno 200 righe, una dimensione temporale, segmenti e metriche di outcome.

L’errore più comune

L’errore ricorrente è usare le join e la set logic come etichetta invece che come processo: presentare grafici senza decisione, metriche senza baseline, conclusioni senza assunzioni esplicite. La domanda che smaschera questo problema è una sola: se il risultato fosse instabile, quale scelta sbaglierei?

Prima di considerare chiuso il ragionamento, controlla di saper dire quale decisione concreta migliora, quale unità di analisi rende il problema misurabile, quale baseline evita una lettura ingenua, quale errore tipico può cambiare la conclusione e quale output consegneresti a uno stakeholder non tecnico.

Riepilogo operativo

Join avanzate, semi-join, anti-join e logica insiemistica non sono solo sintassi: sono scelte semantiche che determinano correttezza e performance. La differenza tra una query che “funziona” e una che “funziona bene” sta nello scegliere l’operazione adatta alla domanda, e quindi nel trasformare l’analisi in una decisione concreta.

[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, verifica completezza, duplicati, timezone, definizioni e segmenti esclusi. Molte analisi falliscono perché il dato misura un comportamento diverso da quello che si crede di osservare.