Elaboração de Consulta
Continuação
7/abril/2006
Modelo
Auto- junção
• Seja a tabela Cliente
Auto-junção
• O código do cliente-indicador é o código
do cliente que indicou aquela empresa.
Ex. MACDONALDS foi indicada pela Brastemp,
BOBS pela Embraco, etc.
A EMBRACO e BRASTEMP não foram indicadas por
ninguém ( cd_cliente_indicador is null )
Auto-junção
• O usuário solicitou um relatório de nome dos
clientes e de seus respectivos indicadores
Cd_cliente, nm_cliente, cd_cliente_indicador
1, Brastemp, null,
2, Embraco, null
3, Mcdonalds, 1
4, Bobs, 2
5, Grendelli, 3
6, Jo Calcados, 4
Auto-junção
• Ou self join
• É usada para efetuar junção de uma
tabela a si mesma, como se fosse duas
tabelas, renomeando temporariamente ,
ao menos, uma tabela na instrução SQL
Auto- junção
SELECT A.nm_cliente as INDICADO ,
I.nm_cliente as INDICADOR
FROM
CLIENTE A inner join CLIENTE I
ON ( I.cd_cliente = A.cd_cliente_indicador)
Auto-junção
Note os apelidos dados aos campos de saída
JOIN ... ON/USING...
CLIENTE
Cd-cliente, nm_cliente
CONTATO
Cd-cliente, cd-contato, nm_contato
1 , Maria
1 , 1, Aldeny
Maria, Aldeny
2 , Joelma
1, 2, Jose
Maria, Jose
3 , Carlos
3, 1, Antonio
Carlos, Antonio
Carlos, Ildefonso
4 , Andre
3, 2, Ildefonso
SELECT nm_cliente, nm_contato FROM cliente
INNER JOIN contato USING(cd_cliente)
Junção Externa
• OUTER JOIN
• É empregada quando há necessidade de
forçar nulos para aparecimento de alguns
valores sem correspondência em uma das
tabelas
• Três variações:
– LEFT OUTER JOIN
– RIGHT OUTER JOIN
– FULL OUTER JOIN
JOIN ... ON/USING...
CLIENTE
Cd-cliente, nm_cliente
1 , Maria
CONTATO
Cd-cliente, cd-contato, nm_contato Maria, Aldeny
1 , 1, Aldeny
Maria, Jose
Carlos, Antonio
2 , Joelma
1, 2, Jose
3 , Carlos
3, 1, Antonio
Joelma, (null)
4 , Andre
3, 2, Ildefonso
Andre, (null)
Carlos, Ildefonso
Listar todos os nomes de clientes e nomes de contatos, quem não
tiver contato, deve aparecer !!!
LEFT [OUTER] JOIN
• Por que LEFT ?
• Sintaxe
– SELECT ... FROM tab1 LEFT [OUTER] JOIN
tab2 ON ( tab1.cd = tab2.o_cd)
• JOIN : Trabalha-se as tabelas de 2 a 2
LEFT [OUTER] JOIN
– SELECT ...
FROM tab1 LEFT [OUTER] JOIN tab2
ON ( tab1.cd = tab2.o_cd)
• Recuperar todos dados da 1ª tabela
(que precede o JOIN: tab1 ) mesmo que o
dado de tab1 não tenha correspondência
em tab2 : um nulo será forçado.
Ex. JOELMA (cliente) => ? (contato)
LEFT [OUTER] JOIN
CLIENTE
Cd-cliente, nm_cliente
CONTATO
Cd-cliente, cd-contato, nm_contato
Maria, Aldeny
1 , Maria
1 , 1, Aldeny
2 , Joelma
1, 2, Jose
Carlos, Antonio
3 , Carlos
3, 1, Antonio
Carlos, Ildefonso
4 , Andre
3, 2, Ildefonso
Joelma,
Maria, Jose
Andre,
SELECT nm_cliente, nm_contato FROM cliente
LEFT [OUTER] JOIN contato USING(cd_cliente)
RIGHT [OUTER] JOIN
• Por que RIGHT ?
• Sintaxe
SELECT ... FROM tab1 RIGHT [OUTER] JOIN
tab2 ON ( tab1.cd = tab2.o_cd)
• Quando se deseja recuperar os todos
dados da 2ª tabela ( que precede o JOIN)
mesmo os dados de tab2 não tenham
correspondência em tab1
RIGHT [OUTER] JOIN
CONTATO
Cd-cliente, nm_cliente
CLIENTE
Cd-cliente, cd-contato, nm_contato
Maria, Aldeny
1 , 1, Aldeny
1 , Maria
Maria, Jose
1, 2, Jose
2 , Joelma
Carlos, Antonio
3, 1, Antonio
3 , Carlos
Carlos, Ildefonso
4 , Andre
Joelma,
3, 2, Ildefonso
Andre,
SELECT nm_cliente, nm_contato FROM contato
RIGHT [OUTER] JOIN cliente USING(cd_cliente)
auto junção com junção externa
• Liste todos os clientes e seus respectivos
indicadores...
• Os clientes que não tem indicadores
também devem aparecer ...
FULL OUTER JOIN
• Recuperação de valores dos dois lados (
duas tabelas) sem descartar as não correspondências
Cd_aluno, nm_aluno
01343, Maria
01445, José
04556, Carlos
05300,Serafim
20030, Jorge
23922,Luiza
25678, Valdir
35876, André
Aluno
JOIN
matricula, nm_func
30134, Ariane
01445, José
34056, Henri
33420,Castro
32310, Joao
33222,Lair
31228, Adir
35976, André
34584, Lea
Func
• Caso 1: cd_aluno,
matricula têm mesmo
domínio de valores
SELECT nm_aluno
FROM
aluno a INNER JOIN func f
ON a.cd_aluno = f.matricula
Apenas
- José
- André
Cd_aluno, nm_aluno
01343, Maria
01445, José
04556, Carlos
05300,Serafim
20030, Jorge
23922,Luiza
25678, Valdir
35876, André
Aluno
JOIN
matricula, nm_func
30134, Ariane
01445, José
34056, Henri
33420,Castro
32310, Joao
33222,Lair
31228, Adir
35976, André
34584, Lea
Func
Select nm_aluno, nm_funcionario
FROM
Aluno a LEFT JOIN Func f
ON a.cd_aluno = f.matricula
Maria,
José, José
Carlos,
Serafim,
Jorge,
Luiza,
Valdir,
André, André
FULL OUTER JOIN
Cd_aluno, nm_aluno
Select nm_aluno, nm_funcionario FROM
matricula, nm_func Aluno a FULL JOIN Func f
ON a.cd_aluno = f.matricula
Maria,
01343, Maria 30134, Ariane
José, José
Carlos,
01445, José
01445, José
Serafim,
04556, Carlos 34056, Henri
Jorge,
05300,Serafim 33420,Castro
Luiza,
20030, Jorge 32310, Joao
Valdir,
23922,Luiza
33222,Lair
André, André,
25678, Valdir 31228, Adir
, Ariane
35876, André 35976, André
, Henri
, Castro
34584, Lea
, Joao
, Lair
, Adir
, Lea
Aluno
Func