WHERE Clausola contro ON quando si utilizza JOIN

Supponendo di avere il seguente codice T-SQL:

SELECT * FROM Foo f INNER JOIN Bar b ON b.BarId = f.BarId; WHERE b.IsApproved = 1; 

Il seguente restituisce anche lo stesso insieme di righe:

 SELECT * FROM Foo f INNER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId); 

Questo potrebbe non essere il campione del caso migliore qui, ma c’è qualche differenza di prestazioni tra questi due?

No, Query Optimizer è abbastanza intelligente da scegliere lo stesso piano di esecuzione per entrambi gli esempi.

È ansible utilizzare SHOWPLAN per verificare il piano di esecuzione.


Tuttavia, è necessario inserire tutte le connessioni di join nella clausola ON e tutte le restrizioni nella clausola WHERE .

Basta fare attenzione alla differenza con i join esterni. Una query in cui un filtro di b.IsApproved (sulla tabella di destra, Bar) viene aggiunto alla condizione ON del JOIN :

 SELECT * FROM Foo f LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId); 

NON è come mettere il filtro nella clausola WHERE :

 SELECT * FROM Foo f LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId) WHERE (b.IsApproved = 1); 

Poiché per i join esterni “non riusciti” a Bar (ovvero dove non è presente b.BarId per un f.BarId ), ciò lascerà b.IsApproved come NULL per tutte le righe di join non riuscite e queste verranno quindi filtrate.

Un altro modo di considerare questo è che per la prima query, LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId) restituirà sempre le righe della tabella SINISTRA, poiché LEFT OUTER JOIN garantisce il Le righe della tabella LEFT verranno restituite anche se il join fallisce. Tuttavia, l’effetto dell’aggiunta (b.IsApproved = 1) al (b.IsApproved = 1) LEFT OUTER JOIN a condizione è quello di eliminare tutte le colonne della tabella destra quando (b.IsApproved = 1) è false, vale a dire secondo le stesse regole normalmente applicate a LEFT JOIN Condizione LEFT JOIN su (b.BarId = f.BarId) .

Aggiornamento : per completare la domanda posta da Conrad, il LOJ equivalente per un filtro OPZIONALE potrebbe essere:

 SELECT * FROM Foo f LEFT OUTER JOIN Bar b ON (b.BarId = f.BarId) WHERE (b.IsApproved IS NULL OR b.IsApproved = 1); 

vale a dire che la clausola WHERE deve considerare sia la condizione se l’unione fallisce (NULL) e il filtro deve essere ignorato, e dove il join ha successo e il filtro deve essere applicato. ( b.IsApproved o b.BarId potrebbe essere testato per NULL )

Ho messo insieme uno SqlFiddle che mostra le differenze tra i vari posizionamenti del filtro b.IsApproved relativo al JOIN .

 SELECT * FROM Foo f INNER JOIN Bar b ON b.BarId = f.BarId WHERE b.IsApproved = 1; 

Questa è la forma migliore per andare. È facile da leggere e facile da modificare. Nel mondo degli affari questo è ciò che vorresti fare. Per quanto riguarda le prestazioni sono uguali però.

Mi sembra che alcuni casi in cui l’ottimizzatore non sia stato abbastanza intelligente anche nelle versioni recenti di MSSQL e la differenza di prestazioni è stata mostruosa.

Ma questa è un’eccezione, la maggior parte del tempo con SQL Server Optimizer risolverà il problema e otterrà il piano giusto.

Quindi mantieni la politica di utilizzare i filtri nella clausola WHERE e ottimizzarli quando necessario.

Ho appena eseguito un test di una query su quattro tabelle: una tabella primaria con tre JOIN INNER e un totale di quattro parametri e ha confrontato i piani di esecuzione di entrambi gli approcci (utilizzando i criteri di filtro in ON di JOIN e quindi anche in la clausola WHERE).

I piani di esecuzione sono esattamente gli stessi. Ho eseguito questo su SQL Server 2008 R2.