Trabalhando com joins no Firebird

Joins, para que servem?

Os Joins servem para juntar tabelas e são implementações originais da linguagem SQL, mas são implementados em vários bancos de dados.

Antes de conhecer os possíveis tipos de Joins que podemos trabalhar vamos conhecer sua sintaxe geral, como mostra a Listagem 1.

01 Select * Form TabelaX 02 <Tipo> Join <TabelaJuncao> On (<Condição>)

Temos um select de todos os registros na TabelaX e na linha seguinte temos o nosso Join, onde é o tipo a ser implementado (que será visto no decorrer desse artigo), a palavra reservada Join seguida de , que deve ser substituída pela tabela que desejamos fazer a junção. O On é a abertura para as condições da junção, que devem ser colocadas em , ou seja, esta será a condição para unir as tabelas.

Para o nosso artigo veremos como isso é feito no banco de dados Firebird.

Vamos criar um banco de dados chamando Conjuntos e adicionaremos três tabelas A, B e C, respectivamente, conforme o script da Listagem 2.

01 /* Tabela A */ 02 CREATE TABLE A ( 03 VALOR INTEGER 04 ); 05 06 /* Tabela B */ 07 CREATE TABLE B ( 08 VALOR INTEGER 09 ); 10 11 /* Tabela C */ 12 CREATE TABLE C ( 13 VALOR INTEGER 14 );

Nossas tabelas são muito simples, temos apenas um campo em cada uma, que é referente ao valor inserido. Essa estrutura irá facilitar a observação e compreensão da comparação do Join com o conjunto de dados. Para popular as tabelas vamos usar o script da Listagem 3.

01 /* Populando Tabela A */ 02 Insert into A (Valor) values(1); 03 Insert into A (Valor) values(2); 04 Insert into A (Valor) values(3); 05 Insert into A (Valor) values(4); 06 Insert into A (Valor) values(5); 07 Insert into A (Valor) values(6); 08 Insert into A (Valor) values(7); 09 Insert into A (Valor) values(8); 10 Insert into A (Valor) values(9); 11 Insert into A (Valor) values(10); 12 13 /* Populando Tabela B */ 14 Insert into B (Valor) values(1); 15 Insert into B (Valor) values(3); 16 Insert into B (Valor) values(5); 17 Insert into B (Valor) values(7); 18 Insert into B (Valor) values(9); 19 Insert into B (Valor) values(11); 20 21 /* Populando Tabela C */ 22 Insert into C (Valor) values(2); 23 Insert into C (Valor) values(4); 24 Insert into C (Valor) values(6); 25 Insert into C (Valor) values(8); 26 Insert into C (Valor) values(10);

Para a tabela A inserimos os números de 1 a 10, para a tabela B foram inseridos os números ímpares de 1 a 11 e para a tabela C somente os números pares de 1 a 10.

Então se fossemos apresentar nossas tabelas A e B em conjuntos teríamos uma representação semelhante à Figura 1.

Figura 1. Representação tabela A e B em conjuntos.

Veremos a seguir alguns tipos de Joins.

1 - Inner Join

O Inner Join é a junção responsável por retornar apenas os valores que estão contidos em A e B, como na Figura 2. O código responsável está na Listagem 4.

Figura 2. Representação de Inner Join

01 Select 02 A.Valor, 03 B.Valor 04 From A 05 Inner Join B on (B.valor = A.Valor)

Definimos que o campo B deve estar contido em um dos campos de A e como resultado temos somente os registros que estão contidos na interseção das duas tabelas.

Podemos implementar quantos Joins forem necessários, então se for necessária uma estrutura que retorne os valores das intercessões dos dados das três tabelas podemos adicionar mais um Join no nosso SQL, como visto na Figura 3.

Figura 3. Representação de Inner Join entre A, B e C

Pela apresentação do conjunto podemos ver que não existe nenhum item na intercessão das três tabelas, então nossa consulta não deve retornar nenhum resultado. Então vamos executar o SQL da Listagem 5 para confirmar nossa teoria.

01 Select 02 A.Valor, 03 B.Valor, 04 C.Valor 05 From A 06 Inner Join B on (B.valor = A.Valor) 07 Inner Join C on (C.valor = A.Valor)

Observação: por mais que os Joins sejam invocados separadamente o resultado é condizente como se eles fossem invocados juntos, utilizando como exemplo a nossa consulta SQL da Listagem 5, que não tem nenhum resultado no retorno. Porém, se executarmos essa consulta primeiro com o Join na tabela B, ela terá um retorno que, depois de um Join com a tabela C, a consulta terá um retorno diferente, com registros. Isso porque as consultas SQL seguem uma estrutura nas respostas das condições, gerando "tabelas temporárias" para as próximas condições, ou seja, ao executar o primeiro Inner Join é gerada uma "tabela temporária" para o próximo Inner e, como as tabelas B e C não possuem nenhum item na interseção, não é obtido nenhum resultado, tendo como resultado o que podemos ver a Figura 4.

Figura 4. Resultado da consulta Inner sem registro contido

Vamos agora fazer uma inserção nas tabelas para que um dos registros esteja contido em todos os conjuntos e assim veremos o funcionamento do Inner com mais de uma tabela. Para isso, vamos adicionar o valor 0 a todas as tabelas utilizando o SQL da Listagem 6, obtendo o novo diagrama de conjuntos da Figura 5.

01 Insert Into A (Valor) Values (0); 02 Insert Into B (Valor) Values (0); 03 Insert Into C (Valor) Values (0);

Figura 5. Representação de Inner Join nos conjuntos com item em comum

Agora basta executar novamente a consulta da Listagem 5 para ver que temos um registro com o valor "0" em todas as colunas.

2 - Left Join

O Left Join é a junção responsável por retornar os valores que estão contidos em A e B, porém "priorizando" o conjunto da esquerda (nesse caso o A), ou seja, retorna todos os valores de A independente se o valor existe em B. Caso o valor seja encontrado em B é trago sua representação, caso contrário, é retornado null para os campos correspondentes a tabela B, como podemos observar na Figura 6.

Figura 6. Representação de Left Join nos conjuntos (Tabelas A e B)

O Left Join entre essas duas tabelas irá retornar todos os valores da tabela A e para a tabela B será retornado apenas os registros que se encontram na interseção, de acordo com o SQL da Listagem 7 e o resultado da Tabela 1.

01 Select 02 A.Valor Valor_de_A, 03 B.valor Valor_de_B 04 From A 05 Left Join B on (B.valor = A.Valor)

Tabela 1. Resultado Left Join das Tabelas A e B

É possível acrescentar mais um Left Join sobre a tabela C, como mostra a Listagem 8, e seu resultado pode ser observado na Tabela 2.

01 Select 02 A.Valor Valor_de_A, 03 B.valor Valor_de_B, 04 C.valor Valor_de_C 05 From A 06 Left Join B on (B.valor = A.Valor) 07 Left Join C on (C.valor = A.Valor)

Tabela 2. Resultado Left Join das tabelas A, B e C

Vemos que os registros das Tabelas A e B se mantiveram e apenas foram adicionados os valores da Tabela C na coluna "Valor_de_C" nos que a condição dentro do On foi atendida, ou seja, apenas os registros em C que o valor seja igual ao de A.

E se quisermos trazer todos os registros que estão contidos na Tabela A através do Left Join, mas não os que estão contidos em B, como representado na Figura 7, será que é possível?

Figura 7. Representação de Left Join Exclusivo nos conjuntos (Tabelas A e B)

A essa técnica é chamada de exclusividade e, no nosso caso, vamos utilizar a exclusividade sobre o Left Join, apresentada na Listagem 9.

01 Select 02 A.Valor Valor_de_A, 03 B.valor Valor_de_B 04 From A 05 Left Join B on (B.valor = A.Valor) 06 WHERE B.valor IS NULL

Tivemos que adicionar uma cláusula Where e a condição que queremos. O resultado é apresentado na Tabela 3.

Tabela 3. Resultado Left Join exclusivo nas tabelas A e B

3- Right Join

O Right Join é a junção responsável por retornar os valores que estão contidos em A e B, porém "priorizando" o conjunto da direita (nesse caso o B). Seu comportamento é o oposto ao Left Join, que prioriza a tabela da esquerda, ou seja, retorna todos os valores de B independente se o valor existe em A, como mostra a Figura 8 e a consulta SQL da Listagem 10.

Figura 8. Representação de Right Join das Tabelas A e B

01 Select 02 A.Valor Valor_de_A, 03 B.valor Valor_de_B 04 From A 05 Right Join B on (B.valor = A.Valor)

Na Tabela 4 podemos observar que todos os registros da tabela B foram tragos junto com os registros da tabela A que tinham relação. Um fato curioso é que o valor 11 da tabela B, por não ter registro correspondente na tabela A, retornou null para o campo correspondente na tabela A.

Tabela 4. Resultado do Right Join das tabelas A e B

Para o Rigth Join podemos também aplicar o conceito de exclusividade, assim obtendo o Right Join Exclusivo, como representando pela Figura 9 pela Listagem 11.

Figura 9. Representação de Right Join Exclusivo entre A e B

01 Select 02 A.Valor Valor_de_A, 03 B.valor Valor_de_B 04 From A 05 Right Join B on (B.valor = A.Valor) 06 WHERE A.valor IS NULL

Podemos ver que a técnica empregada é bem semelhante à do Letf Join Exclusivo,porém na cláusula Where, ao invés de condicionar o valor de B como nulo vamos condicionar o valor de A como nulo.

4 - Full Join

O Full Join é a junção responsável por retornar todos os valores que estão contidos em A e B, independentemente se existe ou não ligação entre as tabelas. Caso o valor seja encontrado em uma das tabelas, o seu respectivo valor é trago, caso contrário é retornado null, podemos observar na Figura 10 e na Listagem 12.

Figura 10. Representação de Full Join nasTabelas A e B

Listagem 12. Full Join das tabelas A e B

Podemos ver que ó código é semelhante ao que já apresentamos. O resultado para essa consulta pode ser observado na Tabela 5.

Tabela 5. Resultado Full Join das tabelas A e B

Para Full Join podemos também aplicar o conceito de exclusividade, assim obtendo o Full Join Exclusivo, como representando pela Figura 11 e pela consultaSQL da Listagem 13.

Figura 11. Representação de Full Join Exclusivo das Tabelas A e B

01 Select 02 A.Valor Valor_de_A , 03 B.Valor Valor_de_B 04 From A 05 Full Join B on (B.valor = A.Valor) 06 where A.valor is null 07 or B.valor is Null

Para a implementação do nosso Full Join Exclusivo podemos empregar duas técnicas em conjunto: a Left Join com Right Join, como vemos na Listagem 14.

01 Select 02 A.Valor Valor_de_A, 03 B.valor Valor_de_B 04 From A 05 Left Join B on (B.valor = A.Valor) 06 Union 07 Select 08 A.Valor Valor_de_A, 09 B.valor Valor_de_B 10 From A 11 right Join B on (B.valor = A.Valor)

Então a consulta SQL da Listagem 13 nos permite implementar um Full Join mais “clean", mas o método apresentado na Listagem 14 é válida para fins educativos.

O mais interessante é que, a partir do Full Join, podemos obter todos os Joins visto até agora com a inclusão de condições na cláusula Where. Para provar isso, nas Listagens 15 a 17 apresentamos todos os joins vistos.

01 Select 02 A.Valor Valor_de_A , 03 B.Valor Valor_de_B 04 From A 05 Full Join B on (B.valor = A.Valor) 06 Where A.valor is not null 07 AND B.valor is not Null

01 Select 02 A.Valor Valor_de_A , 03 B.Valor Valor_de_B 04 From A 05 Full Join B on (B.valor = A.Valor) 06 where A.valor is not null

01 Select 02 A.Valor Valor_de_A , 03 B.Valor Valor_de_B , 04 Coalesce(A.valor ,B.Valor) 05 From A 06 Full Join B on (B.valor = A.Valor) 07 where B.valor is not null

Além de obter os Joins padrões através do Full Join podemos também obter os Joins Exclusivos com a adição da mesma exclusividade original em seu respectivo Join, como mostram as Listagens 18 e 19.

01 Select 02 A.Valor Valor_de_A , 03 B.Valor Valor_de_B 04 From A 05 Full Join B on (B.valor = A.Valor) 06 Where A.valor is not null 07 AND B.valor is Null

01 Select 02 A.Valor Valor_de_A , 03 B.Valor Valor_de_B , 04 Coalesce(A.valor ,B.Valor) 05 From A 06 Full Join B on (B.valor = A.Valor) 07 Where B.valor is not null 08 AND A.valor is Null

5 - Cross Join

O Cross Join foi implementado no Firebird 2.0 e é a junção responsável por gera um produto cartesiano das tabelas A e B, gerando uma junção “em cruz". Esse Join não será representado graficamente como foram feitos nos tipos anteriores, pois o Cross Join gera um grande volume de dados já que faz a combinação de cada item da tabela A com todos os itens da tabela B, independentemente de relacionamento, como pode ser visto na Figura 12.

Na Listagem 20 vemos que o Cross Join tem uma particularidade na sua montagem: a não utilização da On, ou seja, ele não suporta condições, deixando mais simples a sua implementação.

Figura 12.Representação de Cross Join

01 Select 02 A.Valor Valor_de_A , 03 B.Valor Valor_de_B 04 From A 05 Cross Join B

Após a execução da consulta SQL apresentada podemos ver que foram retornados 77 registros.

Uma outra forma de obter um o Cross Join é através de consulta de produtos cartesianos, como pode ser visto na Listagem 21.

01 Select 02 A.Valor Valor_de_A , 03 B.Valor Valor_de_B 04 From A, B

6 - Natural Join

O Natural Join foi implementado no Firebird 2.1 e é uma forma de não ter que especificar os campos de junção na condição On quando os campos das duas tabelas têm um campo com mesmo nome. Para utilizar este método é necessário que os campos a serem utilizados como Join tenham o mesmo nome, como podemos ver na Listagem 22.

01 Select 02 A.Valor, 03 B.valor 04 From A 05 natural Left Join B

O Natural pode ser utilizado para qualquer um dos Joins visto nesse artigo, com a ressalva que deve ser adicionado antes do Join. Além disso, com o Natural, a adição de cláusula On não é necessária.

7- Using Join

O Using foi implementado no Firebird 2.1 e é um outro método que facilita a implantação de condições na cláusula Where, pois utilizamos o Using no lugar do On e especificamos os campos que desejamos que sejam iguais com a utilização de vírgula para separar os campos como, por exemplo: Left Join MinhaTabela Using (Campo1,Campo2). Com isso eliminamos a necessidade de informar o On, como pode ser observado na Listagem 23.

01 Select 02 A.Valor, 03 B.valor 04 From A 05 Left Join B Using(Valor)

O Using evita o problema de ambiguidade caso o campo tenha o mesmo nome nas duas tabelas, assim, apenas precisamos informar o nome do campo, a menos que desejemos utilizar esse campo das duas tabelas como condição.

Com isso encerramos o nosso artigo. Até a próxima.