La trappola di WHERE sui full outer join
Capire perché filtrare in WHERE una colonna di un outer join lo trasforma silenziosamente in un inner join
La trappola di WHERE sui full outer join è una lezione Coding Interview Prep gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento Coding Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso Coding Interview Prep include 4 lezioni in totale.
La trappola in cui cadono tutti
Questo è il bug più comune nei JOIN esterni che gli intervistatori inseriscono appositamente: «Mostra ogni cliente e i suoi ordini del 2024, inclusi i clienti senza ordini nel 2024.»
Il candidato scrive un LEFT JOIN, poi aggiunge un filtro sulla data in WHERE, e i clienti senza ordini del 2024 scompaiono senza che sia evidente. Il LEFT JOIN si degrada silenziosamente in un INNER JOIN. Capire il motivo è un segnale di esperienza avanzata.
La query errata
Ecco l'errore. Sembra ragionevole: mantenere tutti i clienti, unire i loro ordini e filtrare quelli del 2024.
Ma i clienti senza ordini, oppure senza ordini del 2024, scompaiono dal risultato. Il requisito di includerli non viene rispettato.
-- BUG: drops customers with no 2024 order
SELECT c.name, o.id, o.order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01';Perché non funziona
Ricordi l'ordine delle operazioni: il JOIN viene eseguito per primo e produce righe in cui le colonne dell'ordine sono tutte impostate a NULL per i clienti senza corrispondenza. Poi viene eseguito WHERE.
Per un cliente senza corrispondenza, o.order_date è NULL, quindi o.order_date >= '2024-01-01' restituisce UNKNOWN, non true. WHERE mantiene solo le righe il cui risultato è true, quindi elimina le righe con NULL: proprio quelle che il LEFT JOIN aveva preservato.
NULL annulla il filtro
Qualsiasi confronto con NULL restituisce UNKNOWN: NULL >= '2024-01-01' è UNKNOWN, NULL = 5 è UNKNOWN e persino NULL <> 5 è UNKNOWN.
Poiché WHERE lascia passare solo le righe che restituiscono TRUE, ogni riga preservata senza corrispondenza viene eliminata. L'intero scopo del JOIN esterno viene annullato da un solo predicato WHERE su una colonna della tabella a destra.
La soluzione: filtrare in ON
Sposti il filtro nella clausola ON. In questo modo diventa parte della condizione di corrispondenza e viene applicato prima che le righe vengano preservate, quindi i clienti senza corrispondenza sopravvivono con i NULL.
-- CORRECT: filter lives in ON
SELECT c.name, o.id, o.order_date
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.order_date >= '2024-01-01';
-- customers with no 2024 order: kept, NULL orderON e WHERE in una frase
La regola da enunciare durante un colloquio è:
Per la tabella preservata (esterna), le condizioni sull'altra tabella appartengono a ON; le condizioni sulla tabella preservata stessa appartengono a WHERE.
ONdecide cosa conta come corrispondenza (viene eseguito durante il JOIN).WHEREfiltra le righe finali (viene eseguito dopo e rimuove le righe con NULL).
Risultati a confronto
Stessi dati, due posizioni del filtro, risposte diverse. Supponiamo che Carol non abbia ordini del 2024.
- Filtro in WHERE: Carol scompare. Di fatto è un INNER JOIN.
- Filtro in ON: Carol compare una volta con le colonne dell'ordine impostate a NULL, quindi il requisito è rispettato.
La differenza nell'output è proprio il punto centrale della trappola.
-- ON version output
-- Alice | 50 | 2024-03-01
-- Bob | 20 | 2024-05-02
-- Carol | NULL | NULL <-- preservedQuando WHERE è effettivamente corretto
Non tutti i casi di WHERE su un JOIN esterno sono errori. Filtrare la tabella preservata è corretto: non coinvolge i NULL generati dal JOIN.
Anche l'anti-join della lezione precedente usa intenzionalmente WHERE o.id IS NULL per sfruttare proprio questo comportamento. La capacità consiste nel riconoscere in quale dei due casi ci si trova.
-- Fine: filtering the preserved (left) table
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'US';Il criterio per individuarla
Quando esamina un JOIN esterno, controlli la clausola WHERE alla ricerca di predicati sulla tabella non preservata (ad eccezione dei test IS NULL per gli anti-join).
Se vede o.someColumn = ... oppure un test di intervallo o di uguaglianza sul lato esterno in WHERE, sospetti la presenza della trappola. Si chieda: «Questo trasforma il mio LEFT JOIN in un INNER JOIN?» Di solito sì.
Condizioni multiple
È possibile combinare entrambe le posizioni. Le condizioni di corrispondenza sulla tabella a destra vanno in ON; un vero filtro post-JOIN sulla tabella a sinistra va in WHERE. Le due clausole possono coesistere senza problemi.
SELECT c.name, o.id, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.amount > 100 -- match condition
WHERE c.signup_year = 2023; -- preserved-table filterSpiegarlo a voce
Durante il colloquio descriva il meccanismo, non soltanto la correzione:
«Il JOIN viene eseguito per primo e riempie le colonne a destra senza corrispondenza con NULL. Un predicato WHERE su queste colonne restituisce UNKNOWN per le righe con NULL e WHERE scarta le righe il cui risultato non è true, quindi il JOIN esterno si riduce a un INNER JOIN. Inserendo il predicato in ON lo si mantiene come condizione di corrispondenza e si preservano le righe senza corrispondenza.» Questa spiegazione funziona sempre.
Verifica rapida
Deve elencare tutti i clienti e soltanto i loro ordini del 2024, mantenendo anche quelli che non ne hanno avuti.
Riepilogo
Filtrare una colonna della tabella non preservata in WHERE trasforma silenziosamente un JOIN esterno in un INNER JOIN, perché i NULL delle righe senza corrispondenza non soddisfano il predicato (restituiscono UNKNOWN) e WHERE li elimina.
- Le condizioni di corrispondenza sulla tabella esterna vanno in
ON. - I filtri sulla tabella preservata vanno in
WHERE. IS NULLin WHERE è l'anti-join intenzionale, non la trappola.- Spieghi l'ordine delle operazioni per dimostrare di aver compreso il meccanismo.
Domande Frequenti
La lezione «La trappola di WHERE sui full outer join» è gratuita?
Sì — il testo completo di «La trappola di WHERE sui full outer join» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso Coding Interview Prep, passa a CoddyKit PRO. Il corso Coding Interview Prep include 4 lezioni in totale.
Cosa imparerò in «La trappola di WHERE sui full outer join»?
Capire perché filtrare in WHERE una colonna di un outer join lo trasforma silenziosamente in un inner join Eserciti Coding Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.
Ho bisogno di esperienza per iniziare Coding Interview Prep?
Non è richiesta alcuna esperienza precedente. Coding Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.
Quanto tempo richiede la lezione «La trappola di WHERE sui full outer join»?
La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.
Posso scrivere ed eseguire codice in questa lezione Coding Interview Prep?
Sì. Ogni lezione Coding Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.
Tutte le lezioni di questo corso
- LEFT JOIN e conservazione delle righe senza corrispondenza
- Semantica di RIGHT e FULL OUTER JOIN
- Trovare le righe senza corrispondenza (anti-join)
- La trappola di WHERE sui full outer join