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:

Operation	Decision question	Behavior	Syntax
INNER JOIN	Which rows appear in both the sets?	Returns only the intersection	FROM A JOIN B ON ...
LEFT JOIN	Which rows in A have (or don’t have) matches in B?	All rows of A + matches from B (NULL if absent)	FROM A LEFT JOIN B ON ...
SEMI-JOIN	Which rows of A have at least one match in B?	Only rows of A, without columns from B, without duplicates	WHERE EXISTS (SELECT 1 FROM B WHERE ...)
ANTI-JOIN	Which rows of A have no match in B?	Only rows of A without matches in B	WHERE NOT EXISTS (SELECT 1 FROM B WHERE ...)
LATERAL JOIN	For each each row of A, run a subquery that depends on that row	Iterates row by row with a correlated subquery	FROM A CROSS JOIN LATERAL (SELECT ...) AS b
AS-OF JOIN	What was the state of B at the time of the event in A?	Approximate temporal match (closest ≤ timestamp)	FROM A ASOF JOIN B ON A.ts >= B.ts
UNION ALL	Which rows belong to A plus those in B?	Union of sets (keeps duplicates)	SELECT ... UNION ALL SELECT ...
EXCEPT	Which rows of A not are in B?	Set difference	SELECT ... EXCEPT SELECT ...
INTERSECT	Which rows are in both A and B?	Set intersection	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.

Element	Specifica
Unit of analysis	row, partition, window, join, cohort, or temporal metric
Primary signal	correttezza, performance, duplicati, grain, stabilità
Baseline	periodo precedente, gruppo comparabile, benchmark
Decision	query, modello, test SQL, pattern riusabile
Risk	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.

Operational Summary

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]Try it yourself

Use a semi-join (EXISTS) to find users who have made at least one order in the 'Sports' category. Show name and 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

RunCtrl+Enter to runHintSolution

[SQL]Try it yourself

Use an anti-join (NOT EXISTS) to find users who have NEVER made an order in the 'Electronics' category. Order by name.

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

RunCtrl+Enter to runHintSolution

Academic references:

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

Quality control

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.