No exemplo de hoje veremos a utilização de joins no padrão ANSI, que podem
ser usadas em qualquer banco de dados. Banco
de dados relacional, cujo próprio nome diz seu significado,
armazenar, manter dados de forma relacional. E para isso utilizamos
chaves de relacionamento entre as entidades. Fazer um join para
buscar as informações talvez seja uma das operações mais básicas
e mais importantes da programação utilizando um banco de dados.

Join

Join
é o ato de juntar, unir, relacionar, associar, duas ou mais tabelas.
Pode ser promovido entre colunas de tabelas distintas que possuem a
mesma informação. O
ideal é que o Join siga o relacionamento físico entre as
tabelas, mas isso não é uma regra. Um
Join também pode ser promovido por valores indiretos, é o
caso do non-equijoin.

Por
exemplo, relacionando duas tabelas através de uma única coluna:

select table1.column, table2.column
from table1 join table2
    	on (table1.column1 = table2.column1);

Relacionando Composto ocorre entre duas tabelas, utilizando mais de uma coluna:

select table1.column, table2.column
from table1 join table2
    	on (table1.column1 = table2.column1 and table1.column2 = table2.column2);

A sintaxe para montar o comando é:

SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON (table1.column_name = table2.column_name)]|
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)]|
[CROSS JOIN table2];

Onde:

[]
= Define que é
uma Cláusula Opcional.

|
= Divide as
cláusulas, informando que não podem ser declaradas juntas.

Por
exemplo, uma query pode ser montada utilizando NATURAL JOIN ou
FULL OUTER JOIN, ou sem relacionamento. Outro
exemplo, não pode conter uma cláusula NATURAL JOIN juntamente
com CROSS JOIN, em uma mesma query.

Para
exemplificar os oito tipos de joins classificados pela SQL-1999,
utilizaremos como cenário um simples sistema de controle de bens, que incluirá o cadastro de uma pessoa, o cadastro se seu cônjuge,
veículos e imóveis.

Como
regra, tomemos que, em nosso cenário, um bem (veículo ou imóvel)
pode estar cadastrado sem indicar seu proprietário. Vejamos o modelo de
dados:

Modelo
Entidade Relacionamento

Faça
o download dos scripts.

Tipos de Joins:

Os oitos tipos de Join são:

• Equijoins

Relacionamento entre
duas ou mais tabelas. Para consolidar o
relacionamento, as informações contidas nas colunas informadas
devem ser iguais. Por exemplo, seguindo
nosso cenário, quero listar as pessoas que possuem imóveis.

Exemplo:

select cpf_pessoa,p.nome, i.id_imovel,i.descricao,i.valor
  from tb_pessoa p join tb_imovel i
 using (cpf_pessoa);

ou

select i.cpf_pessoa,p.nome, i.id_imovel,i.descricao,i.valor
from tb_pessoa p join tb_imovel i
on (p.cpf_pessoa = i.cpf_pessoa)
and i.id_imovel = 4;

Podemos
utilizar a cláusula using ou a cláusula on para
informar as colunas que serão relacionadas. Quando
utilizada a cláusula using, as colunas das tabelas deverão
conter o mesmo nome para que o relacionamento ocorra.

Também
não poderá ser utilizada a coluna juntamente com alias na
demonstração das tuplas.

Exemplo:

SQL> select i.cpf_pessoa,p.nome, i.id_imovel,i.descricao,i.valor

2 from tb_pessoa p join tb_imovel i

3 using (cpf_pessoa)

4 where i.id_imovel = 4;

select i.cpf_pessoa,p.nome, i.id_imovel,i.descricao,i.valor

from tb_pessoa p join tb_imovel i

using (cpf_pessoa)

where i.id_imovel = 4

ORA-25154: column part of USING clause cannot have qualifier

Adicionando
cláusulas de filtro a um Join

Exemplo:

select cpf_pessoa,p.nome, i.id_imovel,i.descricao,i.valor
from tb_pessoa p join tb_imovel i
using (cpf_pessoa)

where i.id_imovel = 4;

ou

select i.cpf_pessoa,p.nome, i.id_imovel,i.descricao,i.valor
from tb_pessoa p join tb_imovel i
on (p.cpf_pessoa = i.cpf_pessoa)
and i.id_imovel = 4;

Quando
utilizar cláusulas de filtro em um join com a cláusula using,
deve-se utilizar a cláusula where para a primeira cláusula, e a
cláusula and para as outra. 

Já quando usar a
cláusula on no join, pode utilizar tanto a cláusula where
quanto a and para filtro.

Joins entre mais de
duas tabelas

Por exemplo, quero
listar as pessoas que possuem imóveis e veículos.

Exemplo:

select p.cpf_pessoa,p.nome,v.id_veiculo,v.modelo,i.id_imovel,i.descricao
from tb_pessoa p
join tb_veiculo v on (p.cpf_pessoa = v.cpf_pessoa)
join tb_imovel i on (p.cpf_pessoa = i.cpf_pessoa);

• Non-Equijoins

Relacionamento
entre duas ou mais tabelas, onde os valores de uma coluna da primeira
tabela não correspondem diretamente aos valores da coluna da
segunda tabela. Como se fosse um cross
join, que impõem condições para filtrar os registros
desejados.

Em nosso cenário,
vamos exemplificar um non-equijoins fazendo uma query que
liste todos os veículos que seu IPVA é menor que a renda da Pessoa.
Veículos que a pessoa possa pagar o IPVA.

Exemplo:

select p.cpf_pessoa,p.nome,p.renda,v.modelo,v.valor_ipva
from tb_veiculo v
join tb_pessoa p
on (p.renda >= v.valor_ipva);

Neste
exemplo, o banco de dados irá listar todas as possibilidades que
atendam a regra solicitada, mesmo que não haja relação entre os
registros.

• Full
  Outer Joins

Relacionamento
entre duas ou mais tabelas, no qual, mesmo quando não é possível
prover o relacionamento, os dados de ambas tabelas serão listados. Em outras palavras,
quando conseguir prover o relacionamento, lista relacionado, quando
não, lista o registro sem relacionamento. No caso do full outer
join, lista de ambas as tabelas.

Seguindo nosso
cenário, quero listar todas as pessoas e veículos cadastrados, se a
pessoa for proprietária do veiculo, indique.

Exemplo:

select p.cpf_pessoa,p.nome,v.id_veiculo,v.modelo
from tb_pessoa p
full outer join tb_veiculo v
on (p.cpf_pessoa = v.cpf_pessoa);

Veja
que nas linhas 1 e 2 ocorreu o relacionamento entre as tabelas
indicando que a pessoa é proprietária do veículo. Já nas linhas 3
, 4 e 5, a tabela tb_pessoa não tem relacionamento com a
tabela tb_veiculo, mesmo assim as informações são listadas.
O mesmo ocorre com as linhas 6 e 7, a tabela tb_veiculo não
tem relacionamento com a tabela tb_pessoa, mesmo assim as suas
informações são listadas.

• Left
  Outer Joins

Relacionamento
entre duas ou mais tabelas, mesmo quando não é possível prover o
relacionamento, os dados da tabela informada à esquerda são exibidos. No
exemplo abaixo, temos a tabela tb_pessoa à esquerda da tabela
tb_veiculo.

Para
exemplificar, quero listar todas as pessoas, se possuírem veículos,
indique.

Exemplo:

select (p.cpf_pessoa,p.nome,v.id_veiculo,v.modelo
from tb_pessoa p left outer join tb_veiculo v
on (p.cpf_pessoa = v.cpf_pessoa);

Veja
que nas linhas 1 e 2, ocorreu o relacionamento entre as tabelas
listando as pessoas que possuem veículos. Já nas linhas 3, 4 e 5,
a tabela tb_pessoa não tem relacionamento com a tabela
tb_veiculo, mesmo assim as informações das pessoas são
listadas. Diferentemente do full outer joins, as informações
sem relação entre as tabelas não são listadas.

• Right
  Outer Joins

Relacionamento
entre duas ou mais tabelas, utilizando associação entre as chaves,
no qual, mesmo quando não é possível prover o relacionamento, os
dados da tabela informada à direita são exibidos.

No
exemplo abaixo, temos a tabela tb_veiculo à direita da tabela
tb_pessoa. Para
exemplificar, quero listar todos os veículos, se possuir
proprietário, indique.

Exemplo:

select (p.cpf_pessoa,p.nome,v.id_veiculo,v.modelo
from tb_pessoa p right outer join tb_veiculo v
on (p.cpf_pessoa = v.cpf_pessoa);

Veja
que nas linhas 1 e 2, ocorreu o relacionamento entre as tabelas
listando os veículos e seus proprietários. Já nas linhas 3 e, 4,
a tabela tb_veiculo não tem relacionamento com a tabela
tb_pessoa, mesmo assim as informações dos veículos são
listadas.

• Cross
  Joins

Cria
um plano cartesiano com os registros das tabelas informadas. Não
utiliza relação entre as tabelas. Um plano cartesiano é
uma combinação entre os dados da primeira tabela e da segunda tabela. Cada
registro da tabela tb_pessoa será listado combinado com cada
registro da tabela tb_imovel. Por exemplo, temos a tabela
tb_pessoa com 5 registros e a tabela tb_imovel com 4
registros que em sua combinação retornará 20 registros.

Exemplo:

select p.cpf_pessoa,p.cpf_conjuge,p.nome,p.dt_nascimento,

i.id_imovel,i.cpf_pessoa,i.descricao,i.valor
from tb_pessoa p
cross join tb_imovel i;

• Natural
  Joins

Relacionamento
entre duas tabelas – o relacionamento natural. Muita gente confunde
que o natural join segue o relacionamento cadastrado no dicionário
de dados. Mas cuidado! O Natural Join busca relacionar colunas com
mesmo nome, mesmo que estas não contenham chaves entre elas.

Por
exemplo, na tabela tb_imovel e na tabela tb_pessoa,
temos a coluna cpf_pessoa, o relacionamento será formado por
esta coluna, mesmo que não exista chave entre elas.

Exemplo:

 select cpf_pessoa,p.cpf_conjuge,p.nome,p.dt_nascimento,

i.id_imovel,i.descricao,i.valor
from tb_imovel i
natural join tb_pessoa p;

Veja
o erro ocorrido quando tentamos executar utilizando alias para listar
a coluna de relacionamento.

Exemplo:

SQL> select i.cpf_pessoa,p.cpf_pessoa,p.cpf_conjuge,p.nome,

p.dt_nascimento,i.id_imovel,i.descricao,i.valor

2 from tb_imovel i

3 natural join tb_pessoa p;

select i.cpf_pessoa,p.cpf_pessoa,p.cpf_conjuge,p.nome,

p.dt_nascimento,i.id_imovel,i.descricao,i.valor

from tb_imovel i

natural join tb_pessoa p

ORA-25155: column used in NATURAL join cannot have qualifier

As
colunas utilizadas no relacionamento natural devem ser do mesmo
tipo.

• Self-Joins

É
um relacionamento igual ao Equijoin, o que lhe faz ser
Self-Join, é que a segunda tabela é a mesma que a primeira.
Muito conhecido como auto-relacionamento. Em
um Self-Join, não é possível utilizar a clausula using.

Por exemplo, quero
saber qual o cônjuge de uma pessoa cadastrada. O cônjuge também é
uma pessoa cadastrada, e o relacionamento entre as informações
ocorre pela coluna cpf_conjuge.

Exemplo:

select p.cpf_pessoa,p.nome,
c.cpf_pessoa as "CPF Conjuge",c.nome as "Nome Conjuge"
from tb_pessoa p
join tb_pessoa c
on (p.cpf_pessoa = c.cpf_conjuge);