WHERE Clause vs ON ao usar o JOIN

Supondo que eu tenha o seguinte código T-SQL:

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

O seguinte também retorna o mesmo conjunto de linhas:

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

Esta pode não ser a melhor amostra de caso aqui, mas existe alguma diferença de desempenho entre esses dois?

Não, o otimizador de consulta é inteligente o suficiente para escolher o mesmo plano de execução para os dois exemplos.

Você pode usar o SHOWPLAN para verificar o plano de execução.


No entanto, você deve colocar todas as conexões de junit na cláusula ON e todas as restrições na cláusula WHERE .

Apenas tome cuidado com a diferença com junções externas. Uma consulta em que um filtro de b.IsApproved (na tabela à direita, Bar) é adicionado à condição ON do JOIN :

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

NÃO é o mesmo que colocar o filtro na cláusula WHERE :

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

Como para junções externas ‘com falha’ para Bar (ou seja, onde não há b.BarId para um f.BarId ), isso deixará b.IsApproved como NULL para todas essas linhas de junit com falha, e essas linhas serão filtradas.

Outra maneira de ver isso é que, para a primeira consulta, LEFT OUTER JOIN Bar b ON (b.IsApproved = 1) AND (b.BarId = f.BarId) sempre retornará as linhas da tabela LEFT, já que LEFT OUTER JOIN garante a Linhas da tabela ESQUERDA serão retornadas mesmo se a junit falhar. No entanto, o efeito de adicionar (b.IsApproved = 1) à condição LEFT OUTER JOIN em questão é NULL fora de todas as colunas da tabela direita quando (b.IsApproved = 1) for falso, ou seja, conforme as mesmas regras normalmente aplicadas a um LEFT JOIN Condição de (b.BarId = f.BarId) em (b.BarId = f.BarId) .

Atualização : Para completar a pergunta feita pelo Conrad, o LOJ equivalente para um filtro OPCIONAL seria:

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

Por exemplo, a cláusula WHERE precisa considerar a condição de a junit falhar (NULL) e o filtro deve ser ignorado, e onde a junit é bem-sucedida e o filtro deve ser aplicado. ( b.IsApproved ou b.BarId poderia ser testado para NULL )

Eu coloquei um SqlFiddle juntos aqui, o que demonstra as diferenças entre as várias veiculações do filtro b.IsApproved relação ao JOIN .

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

Esta é a melhor forma de ir. É fácil de ler e fácil de modificar. No mundo dos negócios, é com isso que você gostaria de ir. Quanto ao desempenho, eles são os mesmos.

Eu pareço alguns casos em que o otimizador não era inteligente o suficiente, mesmo em versões recentes do MSSQL – e a diferença de desempenho era monstro.

Mas esta é uma exceção, na maioria das vezes o otimizador do SQL Server resolverá o problema e obterá o plano correto.

Portanto, mantenha a política de usar filtros na cláusula WHERE e otimizar quando necessário.

Acabei de executar um teste de uma consulta em quatro tabelas – uma tabela primária com três INNER JOINs e um total de quatro parâmetros, e comparei os planos de execução de ambas as abordagens (usando os critérios de filtro no ON do JOIN e também em a cláusula WHERE).

Os planos de execução são exatamente os mesmos. Eu corri isso no SQL Server 2008 R2.