Banco de Dados Oracle

Mostrar dados a partir de múltiplas tabelas

OBJETIVOS DO CAPÍTULO

1. Conhecer a relação entre as tabelas

2. Descrever os Tipos de Joins

Relação entre as Tabelas CODIGO SOBRENOME NOME COD_USUA COD_GERENTE COD_DEPTO SALARIO

------- --------------- --------------- -------- ----------- ---------- ----------

1 Velasquez Carmen cvelasqu 50 2500

2 Ngao LaDoris lngao 1 41 1450

3 Nagayama MCODIGOori mnagayam 1 31 1400

5 Ropeburn Audry aropebur 1 50 1550

6 Urguhart Molly murguhar 2 41 1200

12 Giljum Henry hgiljum 3 32 1490

14 Nguyen Mai mnguyen 3 34 1525

17 Smith George gsmith 6 41 94060

CODIGO NOME COD_REGIAO

------- --------------- ----------

31 Sales 1

32 Sales 2

34 Sales 4

41 Operations 1

50 Administration 1

CODIGO NOME

------- -------------------------

1 North America

2 South America

3 Africa / MCODIGOdle East

4 Asia

5 Europe

EMPREGADOEMPREGADO

DEPARTAMENTODEPARTAMENTO REGIAOREGIAO

SQL

SOBRENOME COD_DEPTO

--------------- ----------

Velasquez 50

Ngao 41

Nagayama 31

Ropeburn 50

Urguhart 41

Giljum 32

Nguyen 34

CODIGO NOME

------- ---------------

10 Finance

31 Sales

32 Sales

33 Sales

34 Sales

35 Sales

41 Operations

Mostrar dados a partir de tabelas relacionadas

EMPREGADOEMPREGADODEPARTAMENTODEPARTAMENTO

• Um join é uma query que combina linhas a partir de duas ou mais tabelas ou visões.

• O Oracle realiza um join sempre que múltiplas tabelas são consultadas nas query’s a partir da cláusula FROM

• A lista no select das consultas podem selecionar quaisquer colunas a partir destas tabelas. Se qualquer uma das tabelas possuem uma coluna com nome em comum, você você deve qualificar todas as referências desta colunas na query, como o nome da tabela antes das colunas para eliminar ambiguidade.

Joins

Joins

Condições no Join 

•Consutlas com joins devem conter condições com a cláusula WHERE que comparam duas colunas, cada uma de tabela diferente. Tal condição é chamada condição de join.

•Para executar um join, o Oracle combina pares de linhas, cada uma contendo linha de cada tabela, para que possa avaliar para VERDADE a condição de join. As colunas na condição de join não precisam aparecer também na lista do select.

Joins

• Para executar um join de três ou mais tabelas, o Oracle primeiro une duas das tabelas baseadas nas condições de join comparando suas colunas e então une o resultado para outra tabela baseada em condições de join contendo colunas das tabelas unidas e a nova tabela.

• O Oracle continua este processo até que todas as tabelas sejam unidas no resultado.

• O otimizador determina a ordem em que o Oracle une tabelas baseadas nas condições de join, índices nas tabelas e etc.

Joins

• Além de condições de joins, a claúsula WHERE de um join pode conter também outras condições que se referem as colunas de só uma tabela.

• Esta condições podem adicionar restrições as linhas retornadas pelo join da query.

Tipos de Joins

Existem quatro tipos de Joins: NoEquijoins

•Um noequijoins é uma condição contendo um operador de igualdade.

•Um noequijoins combina linhas que tem valores equivalentes para as colunas especificadas só que comparando a partir de uma região de dados, como se estivesse fazendo um IN ou um BETWEEN.

•Dependendo do algoritmo intermo o otimizado escolhe o join a executar.

Tipos de Joins

Equijoins

•Um equijoins é um join com uma condição contendo um operador igualdade.

•Um equijoin combina linhas que tem valores equivalentes para as colunas especificadas.

•Dependendo do algoritimo interno o otimizador escolhe o join a executar.

Tipos de Joins

Self Joins

•Um self join é um join de uma tabela com ela mesma.

•Esta tabela aplica duas vezes na cláusula FROM seguido por um alias na tabela que qualifica nomes de colunas na condição join.

•Para realizar um self join, o Oracle combina e retorna linhas da tabela que satisfaça a condição join.

Tipos de Joins

Outer Joins

•Um outer join extende o resultado de um join simples. •Um outer join retorna todas as linhas que satisfaçam a condição join e aquelas linha a partir de uma tabela que não satisfaçam a condição de join da outra tabela.

Tipos de Joins

Outer Joins (Continuação)

•Tais linhas não são retornadas pelo join simples . Para escrever uma consulta que realiza um outer join de tabelas A e B e retorna todas as linhas a partir de A deve-se aplicar o operador (+) para todas as colunas de B na condição join. para que todas as linhas em A que não tiver linha e B, o Oracle retorna null para qualquer lista selecionada contendo colunas de B.

Tipos de Joins

Produto Cartesiano 

•Se duas tabelas em um join não teve sua condição join escrita, o Oracle retorna um Produto Cartesiano, ou seja, o Oracle combina cada linha de uma tabela com cada linha da outra tabela.

•Um produto cartesiano permite gerar muitas linhas e raramente é usado. Por exemplo, o produto cartesiano de duas tabelasm cada uma com 100 linhas, tem 10000 linhas.

Equijoin• Mostrar linhas de duas ou mais tabela escrevendo uma

condição simples de ligação na cláusula WHERE.

• Especificação de ligação em uma consulta simples. EQUIJOIN (SIMPLE JOIN)

CODIGO NOME COD_DEPTO ------- ----------- --------- 1 Carmen 50 2 LaDoris 41 3 MCODIGOori 31 5 Audry 50 6 Molly 41 12 Henry 32 14 Mai 34 17 George 41 20 Chad 43 22 Eddie 44 24 Bela 45

CODIGO NOME COD_REGIAO------- --------- ---------- 10 Finance 1 31 Sales 1 32 Sales 2 33 Sales 3 34 Sales 4 35 Sales 5 41 Operations 1 42 Operations 2 43 Operations 3 44 Operations 4 45 Operations 5 50 Administration 1

EMPREGADOEMPREGADO DEPARTAMENTODEPARTAMENTO

Primary Foreign Key

Equijoin

Equijoin

SELECT TABELA.COLUNA, TABELA.COLUNA...FROM TABELA1, TABELA2...WHERE TABELA1.COLUNA = TABELA2.COLUNA;

Onde:TABELA.COLUNA: é a tabela e a coluna do dado que será retornado.TABELA1.COLUNA: é a condição dos joins ou relaçõesTABELA2.COLUNA: das tabelas juntas; o nome da tabelaDeverá aparecer antes que o nome da coluna.

SQL> select empregado.sobrenome,empregado.cod_depto, departamento.nome 2 from empregado, departamento 3 where empregado.cod_depto=departamento.codigo;

SOBRENOME COD_DEPTO NOME------------------------- ---------- -------------------Velasquez 50 AdministrationNgao 41 OperationsNagayama 31 SalesRopeburn 50 AdministrationUrguhart 41 OperationsGiljum 32 SalesNguyen 34 SalesSmith 41 OperationsNewman 43 OperationsChang 44 OperationsDancs 45 Operations

Equijoin

Equijoin

CODIGO NOME COD_REGIAO

------- --------------- ----------

31 Sales 1

32 Sales 2

34 Sales 4

41 Operations 1

50 Administration 1

CODIGO NOME

------- -------------------------

1 North America

2 South America

3 Africa / MCODIGOdle East

4 Asia

5 Europe

DEPARTAMENTODEPARTAMENTO REGIAOREGIAO

SQL> SELECT DISTINCT DEPTO.CODIGO "COD.DEPTO", 2 REG.CODIGO "CODIGO REGIAO", 3 REG.NOME "NOME REGIAO" 4 FROM DEPARTAMENTO DEPTO, REGIAO REG 5* WHERE DEPTO.COD_REGIAO=REG.CODIGO;

COD.DEPTO CODIGO REGIAO NOME REGIAO---------- ------------- --------------------------- 10 1 North America 31 1 North America 32 2 South America 33 3 Africa / MCODIGOdle East 34 4 Asia 35 5 Europe 41 1 North America 42 2 South America 43 3 Africa / MCODIGOdle East 44 4 Asia 45 5 Europe 50 1 North America45 Operations

Equijoin

Equijoin

SQL

SOBRENOME COD_DEPTO

--------------- ----------

Velasquez 50

Ngao 31

Nagayama 41

Ropeburn 50

Urguhart 41

Giljum 32

Nguyen 34

CODIGO NOME

------- ---------------

10 Finance

31 Sales

32 Sales

33 Sales

34 Sales

35 Sales

41 Operations

EMPREGADOEMPREGADO DEPARTAMENTODEPARTAMENTO

EquijoinExibir linhas de duas ou mais tabela escrevendo uma condição simples de ligação na cláusula WHERE.

SQL> SELECT EMPREGADO.SOBRENOME, EMPREGADO.COD_DEPTO, 2 DEPARTAMENTO.NOME 3 FROM EMPREGADO, DEPARTAMENTO 4 WHERE EMPREGADO.COD_DEPTO = DEPARTAMENTO.CODIGO 5* AND EMPREGADO.SOBRENOME = 'Smith‘;

SOBRENOME COD_DEPTO NOME------------- ---------- ------------------Smith 41 Operations

Non-EquijoinRetornar l inhas de uma tabela que tenha ligação direta com as linhas de uma outra tabela com um “NOEQUIJOIN”

SQL

GRADE LOSAL HISAL

---------- ---------- ----------

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

EMPREGADOEMPREGADO SALGRADESALGRADE CODIGO NOME SALARIO

------- ----------- ----------

1 Carmen 2500

2 LaDoris 1450

3 MCODIGOori 1400

5 Audry 1550

6 Molly 1200

12 Henry 1490

14 Mai 1525

17 George 940

20 Chad 750

22 Eddie 800

24 Bela 860

Salário na tabela EMPEstá entre menor e maior salário na tabela SALGRADE

Non-Equijoin

Sintaxe:

SELECT TABELA.COLUNA, TABELA.COLUNA...FROM TABELA1, TABELA2...WHERE TABELA1.COLUNA BETWEEN TABELA2.COLUNAAND TABELA2.COLUNA2;

Non-EquijoinRetornando registros com Non-Equijoins

SQL> SELECT E.NOME, E.SALARIO,S.GRADE 2 FROM EMPREGADO E, SALGRADE S 3 WHERE E.SALARIO 4* BETWEEN S.LOSAL AND S.HISAL;

NOME SALARIO GRADE----------- ---------- ----------Molly 1200 1George 940 1Chad 750 1Eddie 800 1Bela 860 1MCODIGOori 1400 2LaDoris 1450 3Audry 1550 3Henry 1490 3Mai 1525 3Carmen 2500 4

11 linhas selecionadas.

Other Joins

CLIENTECLIENTE

SOBRENOME CODIGO

--------------- -------

Velasquez 1

Ngao 2

Nagayama 3

Ropeburn 5

Urguhart 6

Giljum 12

EMPREGADOEMPREGADO

COD_REPR NOME

---------- -------------------------

12 Unisports

14 OJ Atheletics

14 Delhi Sports

11 Womansport

15 Kam's Sporting Goods

15 Sportique

Muench Sports

Other Joins

Retornar linhas de uma tabela que não tenha ligação direta com as linhas de outra tabela com um “OUTER JOIN”Sintaxe:SELECT TABELA.COLUNA, TABELA.COLUNA...FROM TABELA1, TABELA2WHERE TABELA1.COLUNA = TABELA2.COLUNA(+)

OU

SELECT TABELA.COLUNA, TABELA.COLUNA...FROM TABELA1, TABELA2WHERE TABELA1.COLUNA (+) = TABELA2.COLUNA

ONDE:(+)é o operador para junção externa, ou seja, se um dado exitir somente em uma tabela, com esta símbolo será possível retorna esta linha faltando o outro valor

•Acrescente o símbolo OUTER JOIN seguido ao nome da tabela que não tem linhas correspondentes.

Other JoinsSQL> select empregado.sobrenome,empregado.codigo,cliente.nome 2 from empregado, cliente 3 where empregado.codigo(+) = cliente.cod_repr 4* order by empregado.codigo;

SOBRENOME CODIGO NOME--------------- ------- -------------------------------------Giljum 12 UnisportsGiljum 12 Futbol SonoraNguyen 14 OJ AtheleticsNguyen 14 Delhi Sports Womansport Kam's Sporting Goods Hamada Sport Ojibway Retail Sporta Russia Big John's Sports Emporium Kuhn's Sports Sportique

Other JoinsSQL> SELECT CLIENTE.CODIGO "COD CLIENTE", 2 CLIENTE.NOME "NOME CLIENTE", 3 PEDIDO.CODIGO "CODIGO PEDIDO" 4 FROM CLIENTE, PEDIDO 5 WHERE CLIENTE.CODIGO = PEDIDO.COD_CLIENTE(+);

COD CLIENTE NOME CLIENTE CODIGO PEDIDO----------- ------------------------------- ------------- 201 Unisports 1 201 Unisports 2 202 OJ Atheletics 3 203 Delhi Sports 4 204 Womansport 5 204 Womansport 6 204 Womansport 7 205 Kam's Sporting Goods 206 Sportique 207 Sweet Rock Sports 9 208 Muench Sports 10

Self Joins

EMPREGADO (Ger)EMPREGADO (Ger)

SOBRENOME COD_GERENTE

--------------- -----------

Ngao 1

Nagayama 1

Ropeburn 1

Urguhart 2

Giljum 3

Nguyen 3

Smith 6

EMPREGADO (Emp)EMPREGADO (Emp)

CODIGO SOBRENOME

------- ----------

1 Velasquez

2 Ngao

3 Nagayama

5 Ropeburn

6 Urguhart

12 Giljum

14 Nguyen

Self JoinsSQL> SELECT TRABALHADOR.SOBRENOME || ' trabalha para ' || 2 GERENTE.SOBRENOME "EMPREGADOS" 3 FROM EMPREGADO TRABALHADOR, EMPREGADO GERENTE 4 WHERE TRABALHADOR.COD_GERENTE=GERENTE.CODIGO;

EMPREGADOS----------------------------------------------------------Ngao trabalha para VelasquezNagayama trabalha para VelasquezRopeburn trabalha para VelasquezUrguhart trabalha para NgaoGiljum trabalha para NagayamaNguyen trabalha para NagayamaSmith trabalha para Urguhart10