ESSE POST FOI ATUALIZADO, FAVOR ACESSE AQUI: JOINS.

JOINS

O uso dos operadores de joins são fundamentais para realizarmos queries que retornam resultados de múltiplas tabelas do banco.

Desse modo, resolvi elaborar este post para explicar sobre joins, seus tipos e como funcionam nas queries.

CONCEITUANDO

Existem diversos tipos de joins que podemos dividir em três grupos: cross, inner e outer.

Os joins fazem valer as chaves primárias e estrangeiras do banco de dados relacionando as tabelas para retornar os resultados.

O operador join funciona comparando tabelas retornando os valores que derem match na operação. O retorno dos valores mudam de acordo com o tipo de join executado.

Um adendo: JOINS da categoria outer não precisam ter todo o nome completo do tipo: full outer join, podendo ser apenas full join.

Existe uma diferença entre o INNER e OUTER JOIN quanto ao resultado:

  • O inner join não retorna valores nulls no resultado final da query;
  • Inner join retorna apenas os valores que derem match na comparação entre as colunas A e B;
  • Já o Outer join utiliza retorna os valores nulls quando não encontra correspondência da tabela B em A ou A em B, dependendo do tipo de outer join utilizado.

Sempre atribua um apelido para as tabelas que forem utilizadas no join e utilize este mesmo apelido para todas as colunas que utilizar no select, mesmo que esta não seja ambígua (colunas ambíguas em joins são as colunas que se encontram em duas tabelas diferentes, por exemplo a coluna nome pode ser a mesma na tabela produto e venda).

CROSS JOIN

Este será nosso primeiro tipo de join que iremos falar.

Não é um join muito utilizado em queries já que este tipo de join realiza uma operação cartesiana entre as tabelas cruzando resultados. Veja a ilustração como o cross join opera.

Agora, vamos ver a sintaxe e o seu resultado sendo executado no banco de dados.

O cross join é o único join que permite o uso do where na comparação entre as tabelas. Os outros joins utilizam o operador ON.

É possível criar um self join com a mesma tabela igualando no WHERE ou ON quando for executar. Basta atribuir a apelidos diferentes para as tabelas na query.

Por exemplo, na query de self join, o banco retornou 19.972 linhas, que foi o total de combinações possíveis para este auto relacionamento.

No final, o cross join irá multiplicar a quantidade de linhas da tabela A pela quantidade de linhas da tabela B e retornar como resultado todas as combinações possíveis para esta operação. 

O número de colunas não influencia, veja abaixo que ao adicionar mais duas colunas o self join retorna a mesma quantidade de linhas.

INNER JOIN

Este é um tipo de join que cria uma interseção entre as tabelas envolvidas na operação, deixando de fora do resultado final, todos aqueles que não possuem match na comparação dos registros.

É neste join que ocorre a comparação entre a primary key e a foreign key quando executado a query

O processo do inner join é dividido em duas fases:

  1. A primeira fase ele atua igual o cross join, cruzando os dados no formato de um produto cartesiano;
  2. Já na segunda fase ele filtra os resultados eliminando aqueles que não possuem match(ou seja, os valores nulls) no cruzamento de AxB.

No inner join a ordem das tabelas não altera o resultado final, diferente de quando utilizamos outer joins.

Veja um exemplo de inner join abaixo: 

É possível utilizar mais de duas colunas nas operações de join para enriquecer ainda mais o resultado. Só tenha atenção na query, pois quanto mais tabelas maior é a complexidade e além do mais, o resultado da primeira operação serve como base para a segunda operação.

Veja o exemplo abaixo.

Repare que para cada tabela que adicionamos, temos que adicionar outro inner join e outro ON na query para unificar os resultados.

Como o inner join não opera com nulls, somente os dados que estiverem nas três tabelas serão exibidos no final.

Assim como é possível adicionar mais de duas tabelas para comparação, também podemos utilizar mais de uma comparação para filtrar os resultados. Este é um recurso chamado composite join.  Veja abaixo:

Um fato curioso no INNER join quando adicionamos mais um operador de igualdade. Veja:

Nesta query o ON quando igualado a 5 (t1.territoryid = 5) agiu como um where, isso porque, quando utilizamos o inner join, a cláusula ON tem o mesmo processamento lógico que o WHERE na query.

OUTER JOIN

Temos três tipos de outer join:

  1. Left join
  2. Right join
  3. Full join

De acordo com a ‘direção’ do join ditamos a query qual tabela ele vai preservar e com qual vai comparar. 

Por exemplo: se utilizamos o left join, a tabela a esquerda do join será preservada e a tabela a direita do join será a tabela que ele irá comparar. 

Se fôssemos pensar em um diagrama, o left join seria assim:

Vale salientar que a operação tem por base o inner join, que cria a interseção entre as tabelas, mas sem eliminar os valores nulos do resultado final.

Como podemos observar, a coluna salespersonid é uma coluna da tabela da esquerda e foi preservada. Neste resultado vemos que há vendas para clientes porém sem vendedor atribuído, tendo como valor o NULL no local, confirmando que as queries do grupo outer join retornam resultados NULL.

Veja uma query utilizando o right join

Se por um acaso desejar as linhas excluídas da interseção entre as tabelas com algum outer join escolha uma destas três opções para utilizar na query: 

  1. Coluna como primary key, 
  2. Coluna como not null e; 
  3. A coluna utilizada no join. 

Sempre que aparecer um null em uma dessas três colunas podem assumir que foram colunas que não tiveram match na operação de join

FULL JOIN

Fechando a categoria de outer join temos o full join que é um join que agrupa as duas tabelas em uma interseção, mas sem excluir os resultados após a comparação na fase de match das tabelas.

Veja abaixo um exemplo de full join: 

MÚLTIPLOS JOINS EM UMA QUERY

É possível mesclar diversos joins em uma mesma query desde que se atente para a ordem de execução. Os joins nas queries sempre começam da esquerda pra direita e numa query com 4 tipos de join por exemplo, a T2 será a tabela que irá operar com a T3 e assim vai. T1>T2>T3>T4. 

Como o SQL Server processa duas tabelas por vez, em situações de multi join, o resultado de uma será usado para a outra operação.

Inner join e cross join continuam não sendo afetados pela ordem das tabelas.

Vamos analisar como seria uma query com todos os tipos de joins já vistos até aqui.

Lembrem-se: o resultado da operação entre as tabelas T1 e T2 serão utilizados para operar com a tabela T3 e assim por diante.

O próximo exemplo é para reforçar o motivo da ordem e escolha dos joins serem fatores importantes. veja o primeiro resultado e o segundo:

Exemplo 01:

Exemplo 02:

NOTA: os nulls da coluna t1.weight são oriundos da própria coluna, uma vez que ela aceita valores nulos como entrada. Então, mesmo executando inner, acaba retornando. Mas repare que todos os produtos ali possuem correspondência completa.

Veja que ao executar o inner join, acabamos eliminando as linhas que eram nulls, mas se elas forem importantes e requisitadas, um jeito de solucionar seria da seguinte forma. 

Um outro recurso bem interessante de se utilizar é separar o processamento lógico de forma independente. Veja o resultado abaixo:

Assim conseguimos retornar os produtos, suas subcategorias e categorias de forma completa incluindo os produtos que não possuíam categoria e subcategoria  atrelado.

Não se atenha ao resultado, tente apenas entender a lógica da query e como ela é processada. Este exemplo foi utilizado apenas para demonstração e uma forma de se pensar em como organizar a query para os diversos tipos de join.

EVITANDO BUG COM OUTER JOIN

Muitas das vezes ocorre de uma query com outer join ter um where com a tabela não preservada, o que significa que muitos atributos que ficaram nas linhas de fora da interseção são nulas e quando especificadas no where do tipo ‘where t2.col01 = ‘values’; Todos os valores que forem nulos serão descartados porque o where trata como desconhecido. O que tornaria a query com outer join um inner join.

Veja o seguinte cenário: a query abaixo retorna produtos, pedidos, vendedor, cliente e data do pedido, sendo que o where foi feito pela tabela preservada retornando o resultado como um right join deve retornar.

Veja inclusive o plano de execução da query acima confirmando que a query foi executada com um right join :

Agora veja o que acontece quando declaramos no where a coluna t1.salespersonid e compare com o resultado retornado e repare também na quantidade de linhas retornadas.

Agora, veja também o plano de execução desta query e repare num fato curioso.

O engine do SQL Server executou como se fosse um  inner join devido a cláusula where na query eliminando os valores nulos e reduzindo o resultado final da consulta.

Então, quando for utilizar where em queries com outer joins avalie qual dos joins sua query está utilizando e dependendo filtre pela coluna da tabela preservada.

Conclusão

Este foi um post bem extenso, mas completo sobre os tipos de joins  e como cada um deles afeta o resultado.

Mostrei também que há como utilizar diversos joins  na mesma query e o quão importante é observar a disposição das tabelas e sua relação com o operador, além disso, mostrei a interação que um inner join possui com outer e como podemos solucionar isso.

E por fim, um erro que pode ser muito comum mas que pode causar uma certa dor de cabeça que é o mau uso do where quando executamos consultas com joins.

Espero que tenha gostado do post e saúde!

Link para o script!