Filtro no WHERE ou ON?

Continuo ministrando um treinamento e gostaria de deixar aqui um comentário bacana que apareceu hoje na aula. Estava comentando sobre o JOIN e a possibilidade de se usar filtros específicos na cláusula ON.

Para o exemplo eu me baseei no Northwind e executei duas consultas “diferentes”:

1:  SELECT
2:  P.CategoryID,
3:  P.ProductName,
4:  C.CategoryName,
5:  P.UnitPrice
6:  FROM Products AS P
7:  INNER JOIN Categories AS C
8:  ON P.CategoryID = C.CategoryID
9:  and P.UnitPrice > 30

1:  SELECT
2:      P.CategoryID,
3:      P.ProductName,
4:      C.CategoryName,
5:      P.UnitPrice
6:  FROM Products AS P
7:  INNER JOIN Categories AS C
8:  ON P.CategoryID = C.CategoryID
9:  where P.UnitPrice > 30

Notem que a diferença está onde eu especifiquei o filtro do preço, através da cláusula ON do INNER JOIN ou da cláusula WHERE. Olhando sob a perspectiva do processamento lógico na segunda consulta, imaginamos um inner join sendo executado entre as duas tabelas e na tabela virtual resultante da junção, o filtro unitprice > 30 sendo aplicado.

Como processamento lógico é diferente do processamento físico, o SQL Server aplica o mesmo plano de execução (figura 01) para ambas as consultas, aplicando o filtro durante o clustered índex scan da tabela de produtos. Esse é um comportamento esperto do SQL Server, pois restringe o número de registros que irão fluir entre os passos do plano de execução, gerando tabelas virtuais menores.

(Figura 01 – plano de execução com INNER JOIN)

Explicado esse ponto, eu já partia para o próximo slide quando um aluno mencionou um caso muito importante. Ele disse “eu já fiz algo semelhante quando estava trabalhando com um OUTER JOIN e obtive resultados diferentes”. Ele não poderia estar mais certo, vamos supor as duas consultas abaixo.

SELECT
P.CategoryID,
P.ProductName,
C.CategoryName,
P.UnitPrice
FROM Products AS P
LEFT OUTER JOIN Categories AS C
ON P.CategoryID = C.CategoryID
WHERE P.UnitPrice > 30

SELECT
P.CategoryID,
P.ProductName,
C.CategoryName,
P.UnitPrice
FROM Products AS P
LEFT OUTER JOIN Categories AS C
ON P.CategoryID = C.CategoryID
AND P.UnitPrice > 30

Na primeira consulta o SQL Server retorna todos os 77 produtos existentes no Northwind e após realizar o join, que eventualmente poderia trazer um registro sem categoria (que não atendeu a cláusula P.CategoryID = C.CategoryID), aplica o filtro UnitPrice > 30, que somente permite que 24 registros sejam retornados.

Já no segundo caso, o SQL Server aplica o filtro composto que une as duas tabelas e como resposta do “inner join”, somente retorna 24 registros que atendem a condição. Porém, como estamos falando de um LEFT OUTER JOIN, que também retorna os registros da tabela da esquerda que NÃO atenderam a cláusula descrita, o SQL Server preserva a tabela Products, retornando os 77 registros, sendo 53 com nome da categoria como NULL (veja figura 02). Note que aqui o filtro está sendo aplicado no join e não no cluster index scan da tabela de produtos.

(Figura 02 – Plano de execução com OUTER JOIN e filtro no ON)

Quando estamos falando de um OUTER JOIN, a semântica de aplicar um filtro na cláusula WHERE ou no ON é diferente do INNER JOIN, então você deve ficar atento para não introduzir pequenos bugs no seu código.

[]s
Luciano Caixeta Moreira
luticm79@hotmail.com