Outer Join Condition vs. Where Condition
Analizziamo ora una casistica simile a quella mostrata in un mio post precedente. Consideriamo un tipico problema che si risolve con l’OUTER JOIN, avendo a disposizione due tabelle (Person.Contact e Sales.ContactCreditCard) di AdventureWork in relazione tra loro: si vogliono conoscere tutti i contatti che non hanno una carta di credito. La query che risolve il problema sarà:
USE AdventureWorks;
SELECT
C.ContactID
, C.Title
, C.FirstName
, C.MiddleName
, C.LastName
, CC.CreditCardID
FROM
Person.Contact AS C
LEFT JOIN Sales.ContactCreditCard AS CC
ON C.ContactID = CC.ContactID
WHERE
CC.ContactID IS NULL ;
la quale restituisce 854 righe.
Se volessi spostare la condizione where nella clausula join, otterrei la seguente query:
SELECT
C.ContactID
, C.Title
, C.FirstName
, C.MiddleName
, C.LastName
, CC.CreditCardID
FROM
Person.Contact AS C
LEFT JOIN Sales.ContactCreditCard AS CC
ON C.ContactID = CC.ContactID
AND CC.ContactID IS NULL ;
che restituisce invece 19972 righe, e cioè TUTTI i contatti! Facendo riferimento al suddetto post, mi aspettavo di ottenere gli stessi risultati! Cosa succede?
Per comprendere «l’arcano» :) , bisogna conoscere come una query viene processata a livello logico dall’engine. Per un maggiore approfondimento rimando alla lettura del libro “Inside Microsoft SQL Server 2008: T-SQL Querying” (il primo capitolo dell’edizione precedente del libro lo trovate qui). Nel nostro caso, l’engine esegue i seguenti passi:
- Genera il prodotto cartesiano (cross join) delle tabelle C e CC; consideriamo il risultato di tale prodotto la tabella virtuale V1.
- Applica la condizione di join alle righe di V1 e le righe risultanti apparterranno alla tabella virtuale V2. Si osservi che, valendo la logica a tre valori (spiegata più in dettaglio qui), verranno filtrate le righe di V1 per cui la suddetta condizione restituisce TRUE, escludendo FALSE e UNKNOWN. Ora, la condizione di join è definita come: (C.ContactID = CC.ContactID AND CC.ContactID IS NULL). La prima uguaglianza restituisce tutte quelle righe in cui CC.ContactID è un valore non nullo, perché, se fosse nullo, la condizione C.ContactID = NULL verrebbe valutata a UNKNOWN e dunque le relative righe sarebbero scartate dal filtro. Allo stesso tempo, si richiede che (CC.ContractID IS NULL), ma le due espressioni non possono essere vere allo stesso tempo, dunque la nostra condizione di join restituirà un insieme di righe V2 vuoto.
Per verificare che tale condizione esclude tutte le righe, sia nel caso di C.ContactID numerico, sia sia nel caso di C.ContactID uguale a NULL, è sufficiente lanciare la seguente query:
SELECT
CASE WHEN (1 = 1 AND 1 IS NULL) THEN 'RIGA INCLUSA'
ELSE 'RIGA SCARTATA'
END AS ValoreNumerico,
CASE WHEN (NULL = NULL AND NULL IS NULL) THEN 'RIGA INCLUSA'
ELSE 'RIGA SCARTATA'
END AS ValoreNullo
per osservare i risultati predetti:
- Poiché si tratta di un outer join, l’engine aggiunge alle righe di V2 tutte le righe della tabella preserved (in questo caso C perché si tratta di un left join) che non match-ano con CC. Sia la tabella virtuale V3 il risultato di tale operazione. Poiché V2 è una tabella vuota, vuol dire che il matching al punto 2 ha restituito “nessun contatto”; dunque il “non matching” restituirà “tutti i contatti”. Dunque, V3 conterrà tutti i contatti.
Occhio dunque ad includere la condizione di WHERE in quella di JOIN quando si ha a che fare con un outer join!