Utilizando Joins em SQL Veja neste artigo, dicas de como utilizar corretamente Joins em instruções SQL. Para aqueles que tinham dúvidas quanto ao funcionamento dos JOIN's das tabelas no SQL, desenvolvi um pequeno tutorial de apoio, que segue abaixo: Tabelas e seus registros: TABELA_A |CODIGO | NOME | | 1 | UM | | 2 | DOIS | | 3 | TRES | | 4 | QUATRO | | 5 | CINCO | TABELA_B |LANCA | CODIGO | VALOR| | 1 | 1 | 1.000| | 2 | 1 | 2.000| | 3 | 1 | 5.000| | 4 | 2 | 4.000| | 5 | 2 | 9.000| | 6 | 3 | 7.000| | 7 | 5 | 4.000| | 8 | 8 | 7.000| Para a relação entre as tabelas temos: ∙ 3 registros para a empresa 1 (que existe na tabela de empresas); ∙ 2 registros para a empresa 2 (que existe na tabela de empresas); ∙ 1 registros para a empresa 3 (que existe na tabela de empresas); ∙ 0 registros para a empresa 4 (que existe na tabela de empresas); ∙ 1 registros para a empresa 5 (que existe na tabela de empresas); ∙ 1 registros para a empresa 8 (que NÃO existe na tabela de empresas); Agora vamos ver como ficariam as pesquisas* (SELECT's) com os JOIN's ( INNER, [ LEFT | RIGHT | FULL ] OUTER ):
* Para tais pesquisas vamos usar a seguinte linguagem: SELECT [CAMPOS] FROM "TABELA_DA_ESQUERDA" [INNER] JOIN | {LEFT | RIGHT | FULL } [OUTER]} JOIN "TABELA_DA_DIREITA" 1) INNER JOIN: SELECT A.NOME "A.NOME", B.VALOR "B.VALOR" FROM TABELA_A A INNER JOIN TABELA_B B ON B.CODIGO = A.CODIGO |A.NOME | B.VALOR | 1.|UM | 1.000| 2.|UM | 2.000| 3.|UM | 5.000| 4.|DOIS | 4.000| 5.|DOIS | 9.000| 6.|TRES | 7.000| 7.|CINCO | 4.000| Nas pesquisas com INNER JOIN o resultado trará somente as linhas que sejam comum nas 2 tabelas, ligadas pelos campos das tabelas em questão na pesquisa. 2) LEFT OUTER JOIN: SELECT A.NOME "A.NOME", B.VALOR "B.VALOR" FROM TABELA_A A LEFT OUTER JOIN TABELA_B B ON B.CODIGO = A.CODIGO A |A.NOME | B.VALOR | 1.|UM | 1.000| 2.|UM | 2.000| 3.|UM | 5.000| 4.|DOIS | 4.000| 5.|DOIS | 9.000| 6.|TRES | 7.000| 7.|QUATRO | <NULL>| 8.|CINCO | 4.000| Nas pesquisas com LEFT OUTER JOIN o resultado trará todas os registros que estejam na tabela da esquerda do JOIN (neste caso é a TABELA_A) ao menos 1 vez, mesmo que não tenham registros na
tabela da direita do JOIN (neste caso é a TABELA_B) ligadas à tabela da esquerda, como é o caso da linha 7. 3) RIGHT OUTER JOIN: SELECT A.NOME "A.NOME", B.VALOR "B.VALOR" FROM TABELA_A A RIGHT OUTER JOIN TABELA_B B ON B.CODIGO = A.CODIGO |A.NOME | B.VALOR | 1.|UM | 1.000| 2.|UM | 2.000| 3.|UM | 5.000| 4.|DOIS | 4.000| 5.|DOIS | 9.000| 6.|TRES | 7.000| 7.|CINCO | 4.000| 8.|<NULL> | 7.000| Nas pesquisas com RIGHT OUTER JOIN o resultado trará todas os registros que estejam na tabela da direita do JOIN (neste caso é a TABELA_B) ao menos 1 vez, mesmo que não tenham registros na tabela da esquerda do JOIN (neste caso é a TABELA_A) ligadas à tabela da direita, como é o caso da linha 8. 4) FULL OUTER JOIN: SELECT A.NOME "A.NOME", B.VALOR "B.VALOR" FROM TABELA_A A FULL OUTER JOIN TABELA_B B ON B.CODIGO = A.CODIGO |A.NOME | B.VALOR | 1.|UM | 1.000| 2.|UM | 2.000| 3.|UM | 5.000| 4.|DOIS | 4.000| 5.|DOIS | 9.000| 6.|TRES | 7.000| 7.|QUATRO | <NULL>| 8.|CINCO | 4.000| 9.|<NULL> | 7.000| Nas pesquisas com FULL OUTER JOIN o resultado trará todas os registros, ao menos 1 vez, que estejam nas 2 tabelas, tanto a da esquerda do JOIN (neste caso é a TABELA_A) quanto a da direita do JOIN
(neste caso é a TABELA_B), como é o caso das linhas 7 e 9. O FULL poderíamos dizer que é uma junção entre o LEFT OUTER JOIN e o RIGHT OUTER JOIN.
Explorando os Tipos de Join – Parte I Veja neste artigo quatro tipos de Join: inner join, left join, right join e full outer join. Join é a ferramenta básica de trabalho nos bancos de dados relacionais. É através dos diferentes tipos de join que conseguimos recuperar dados de uma ou mais tabelas com o auxílio dos relacionamentos existentes. Nessa matéria discutiremos os 4 modêlos de join (inner join, left join, right join e full outer join); aprenderemos também como escolher o tipo certo de join para cada situação. Para os exemplos a seguir serão consideradas duas tabelas : produto e venda. A tabela venda como o prórpio nome sugere irá registrar vendas de itens cadastrados na tabela produto, mas pela característica do negócio também serão permitidas vendas de produtos sem cadastro. O script abaixo irá criar e popular as tabelas venda e produto:
/* criação da tabela produto */ create table produto ( cod_produto int primary key, descr_produto varchar (20) ) /* criação da tabela venda.*/ create table venda ( id_venda int identity primary key, cod_produto int , qtde int, vlr_unit dec(9,2) ) /* populando a tabela produto */ insert into produto values (101001,'Livro1') insert into produto values (101002,'Livro2') insert into produto values (101003,'Livro3') insert into produto values (101004,'Livro4') insert into produto values (101005,'Livro5') /* populando a tabela venda */ insert into venda (cod_produto,qtde,vlr_unit) values (101001,2,14.00) insert into venda (cod_produto,qtde,vlr_unit) values (101002,1,20.50)
insert into venda (cod_produto,qtde,vlr_unit) values (101003,4,12.00) insert into venda (cod_produto,qtde,vlr_unit) values (101030,6, 8.00) insert into venda (cod_produto,qtde,vlr_unit) values (101031,1,44.00) O relacionamento entre as tabelas produto e venda pode ser visualizado na figura1. Figura1: Relacionamento entre as tabelas produto e venda
cadastrados (tab.produto)
vendidos (tab.venda)
vendidos sem cadastro
cadastrados e não vendidos
vermelho + verde
verde + azul
azul
vermelho
10101
10101
10102
10102
10103
10103
10104
10104
10105
10105 10130
10130
10131
10131
Observemos o seguinte:
A área em vermelho representa os produtos que foram cadastrados mas não foram vendidos.Para resgatar esses produtos devemos utilizar um left join , ligando um filtro para listar SOMENTE as linhas EXISTENTES na tabela produto SEM CORRESPONDÊNCIA na tabela venda ;
A área em azul representa produtos que foram vendidos e não possuem cadastro. Para resgatar esses produtos deveremos utilizar um right join ligando um filtro para listar SOMENTE as linhas EXISTENTES na tabela venda SEM CORRESPONDÊNCIA na tabela produto ; A área em verde representa a intersecção dos dois conjuntos, isto é, produtos que foram vendidos e possuem cadastro. Para selecionar as linhas que possuem equivalência nas duas tabelas utilizamos o inner join ; Para representar a união de todos os produtos com todas as vendas, independentemente de relacionamento (áreas em vermelho+verde+azul) utilizamos um full outer join. Com o auxílio da figura1 listada a seguir e, à partir do script para criação das tabelas produto (cod_ produto , descr_ produto ) e venda (cod_ produto , qtde, vlr_unit) forcecido na matéria anterior (Explorando os tipos de Join – Parte I) ...
... vamos praticar a execução diferentes tipos de join para responder as questões a seguir : 1. Listar os produtos que foram vendidos e possuem cadastro A resposta está na interseção entre produto e venda, representado na figura1 pela cor verde . Será resolvido com um inner join : select produtos_vendidos_com_cadastro = p.cod_produto from produto p inner join
venda v on p.cod_produto = v.cod_produto produtos_vendidos_com_cadastro 101001 101002 101003 (3 row(s) affected) 2. Listar os produtos que foram vendidos sem cadastro Deveremos resgatar os produtos da área em azul , que não possuem correspondência na tabela produto . Será utilizado um right join para selecionar todas as linhas da tabela venda ; o filtro .... ... where p.cod_ produto is null nos permitirá filtrar somente as vendas de produtos sem cadastro : select produtos_vendidos_sem_cadastro = v.cod_produto from produto p right outer join venda v on v.cod_produto = p.cod_produto where p.cod_ produto IS NULL produtos_vendidos_sem_cadastro 101030 101031 (2 row(s) affected) 1. Listar os produto com cadastro que NÃO foram vendidos A resposta para essa pergunta está na área em vermelho da figura1. Para sua resolução foi empregado um left join , mas o mesmo resultado poderia ser obtido com um right join , bastando para isso inverter a ordem das tabelas ... from venda v right outer join produto p on .... select produtos_com_cadastro_sem_venda = p.cod_produto from produto p left outer join venda v on v.cod_produto = p.cod_produto where v.cod_produto IS NULL produtos_com_cadastro_sem_venda
101004 101005 (2 row(s) affected) Algumas dicas interessantes: Left Join irá listar todas as linhas da primeira tabela relacionada no join, logo após a cláusula from. Right Join irá listar todas as linhas referentes à segunda tabela relacionada no join ; Em ambos os casos , quando a linha listada não possuir equivalência na tabela destino , as colunas da tabela destino aparecerão com valores nulos 4. Formule um join para responder às questões abaixo Quais foram os produtos vendidos que possuíam cadastro ; Quais foram os produtos vendidos que não possuíam cadastro ; Quais foram os produtos com cadastro que não foram vendidos Além do código do produto, liste também sua descrição ( produtos sem cadastro deverão aparecer com a descrição “ sem cadastro ”) e, numa coluna de nome observação , informe uma das três possibilidades: venda com cadastro (= item a), venda sem cadastro (= item b) ou produto com cadastro sem venda (= item c) select produto = case when p.cod_ produto is nul then v.cod_ produto else p.cod_ produto end, descricao = case when p.descr_ produto is NOT null then p.descr_produto else 'sem cadastro' end, observacao = case when p.cod_ produto is NOT null and v.cod_ produto is NOT null then 'venda com cadastro' when p.cod_ produto is NOT null and v.cod_ produto is null then 'produto com cadastro sem venda' else 'produto sem cadastro com venda' end from produto p full outer join venda v on p.cod_ produto = v.cod_ produto produto descricao observacao 101001 Livro1 venda com cadastro 101002 Livro2 venda com cadastro 101003 Livro3 venda com cadastro
101004 Livro4 produto com cadastro sem venda 101005 Livro5 produto com cadastro sem venda 101030 sem cadastro produto sem cadastro com venda 101031 sem cadastro produto sem cadastro com venda (7 row(s) affected) E o CROSS JOIN ?
O cross join irá relacionar todos os elementos da primeira tabela com todos os elementos da segunda tabela; num movimento conhecido por Produto Cartesiano. Pode ser que você nunca precise utilizar esse tipo de join, mas é bom saber que existe e em que situações poderia ser utilizado. Vamos supor que você possua uma tabela com siglas das unidades da federação. Você quer emitir uma listagem onde os produtos apareçam ao lado de cada sigla da federação. A tabela de unidades da federação e o select para produzir o relatório encontramse listados a seguir. /* criação da tabela uf */ create table uf ( sigla char(2)) insert into uf values ('SP') insert into uf values ('RJ') /* select com o Produto Cartesiano entre a tabela produto e uf */ select * from produto cross join uf cod_produto descr_produto sigla 101001 Livro1 SP 101002 Livro2 SP 101003 Livro3 SP 101004 Livro4 SP 101005 Livro5 SP 101001 Livro1 RJ 101002 Livro2 RJ 101003 Livro3 RJ 101004 Livro4 RJ 101005 Livro5 RJ (10 row(s) affected)
Conclusão Antes de escolher o tipo de join para sua query, visualize o que realmente você deseja. Conhecer os tipos de join existentes para utilizálos quando a situação exige pode ser a diferença entre o certo e o errado, o rápido e o lento.
Leia mais em: Explorando os Tipos de Join – Parte II http://www.devmedia.com.br/explorandoostiposdejoinparteii/2524#ixzz2qcE2uuVl