006_Aula_05_Mai_2016 PrgBD

(1)

Junção de Dados (Join)

Criação do Cenário

• Como comentamos na aula passada, vamos criar o cenário para a aula de hoje, para isso inicie criando o usuário “AULA0505” com a senha “teste” e conceda o grant de DBA para esse usuário.

SQL> CREATE USER aula0505 IDENFIFIED BY teste;

(2)

Junção de Dados (Join)

Criar a Tebela Empregados

• Crie a tabela empregados baseada na tabela “HR.EMPLOYEES”, contendo a estrutura e os dados dela.

CREATE TABLE empregados AS

SELECT * FROM hr.employees; • Selecione todos os dados da tabela.

(3)

Junção de Dados (Join)

Explorando a tabela EMPREGADOS

• Observe que a tabela “EMPREGADOS” tem o atributo “DEPARTMENT_ID”, esse campo se refere ao código do departamento do empregado.

• Veja agora os dados da tabela “HR.DEPARTMENTS”.

SELECT * FROM hr.departments;

• Veja que essa tabela também tem o atributo “DEPARTMENT_ID” e tem ainda o atributo “DEPARTMENT_NAME”

(4)

Junção de Dados (Join)

Criando a tabela Departamentos

• Vamos fazer também uma cópia da tabela “HR.DEPARTMENTS” com o nome de “DEPARTAMENTOS”.

CREATE TABLE departamentos AS

SELECT * FROM hr.departments;

(5)

Junção de Dados (Join)

Unindo Dados de Duas Tabelas

• Como vimos, na tabela “EMPREGADOS” existe a coluna

“DEPARTMENT_ID” e na tabela “DEPARTAMENTOS” também existe essa coluna e ainda a coluna “DEPARTMENT_NAME”

• Vamos fazer uma query que nos mostre os atributos:

“EMPLOYEE_ID”, “FIRST_NAME”, “LAST_NAME”, “DEPARTMENT_ID” esses da tabela “EMPREGADOS”, e também os atributos

“DEPARTMENT_ID”, “DEPARTMENT_NAME” da tabela “DEPARTAMENTOS”.

(6)

Junção de Dados (Join)

Unindo Dados de Duas Tabelas

• SELECT empregados.employee_id, empregados.first_name, empregados.last_name,

empregados.department_id,

departamentos.department_name FROM empregados, departamentos

ORDER BY 1;

• Como estamos utilizando dados de duas tabelas, há a necessidade de à frente de cada atributo informar de qual tabela o atributo deve ser retirado.

(7)

Junção de Dados (Join)

Unindo Dados de Duas Tabelas

• Você poderá perceber que essa query retornou 2889 linhas, que é o produto cartesiano das 107 linhas da tabela “EMPREGADOS” com 27 linhas da tabela “DEPARTAMENTOS”.

• Para que possa ser efetuado um JOIN de duas ou mais tabelas, o banco de dados sempre começa por um produto cartesiano, mas não é isso que queremos ver, o que precisamos é somente as linhas em que o atributo “DEPARTMENT_ID” seja igual nas duas tabelas, então devemos

reconstruir a query colocando essa condição na cláusula WHERE, a query ficaria então dessa forma:

(8)

Junção de Dados (Join)

Unindo Dados de Duas Tabelas

• SELECT empregados.employee_id empregados.first_name, empregados.last_name, empregados.department_id, departamentos.department_id, departamentos.department_name FROM empregados, departamentos

WHERE empregados.department_id = departamentos.department_id ORDER BY 1;

(9)

Junção de Dados (Join)

Unindo Dados de Duas Tabelas

• Agora a query nos mostra o que precisamos, somente as 106 linhas (Uma das linha da tabela empregados tem o valor NULL na coluna

“DEPARTMENT_ID”, não tendo dessa forma um valor correspondente na tabela departamentos)

(10)

Junção de Dados (Join)

Unindo Dados de Duas Tabelas

• Você pode simplificar o modo de escrever a query atribuindo um alias (apelido) para a tabela.

SELECT e.employee_id

e.first_name,

e.last_name,

e.department_id,

d.department_id,

d.department_name

FROM empregados e, departamentos d

WHERE e.department_id =

d.department_id

ORDER BY 1;

(11)

Junção de Dados (Join)

O comando JOIN

• Nas queries anteriores finalizamos o SELECT extraindo o que

precisávamos do produto cartesiano utilizando a cláusula WHERE.

• No Oracle era somente dessa forma que os joins eram efetuados até a versão 8 do banco dados, a partir da versão 9 a Oracle atendendo

determinações da SQL1999 da ISO, introduziu o comando JOIN.

• A query que efetuamos anteriormente ficaria dessa forma utilizando o JOIN:

(12)

Junção de Dados (Join)

O comando JOIN

• SELECT employee_id, first_name, last_name, department_id, department_name FROM empregados JOIN departamentos USING (department_id) ORDER BY 1;

• O resultado dessa query é exatamente igual ao anterior que utilizamos a cláusula WHERE.

(13)

Junção de Dados (Join)

O comando JOIN

• Conforme comentamos anteriormente, a query executada irá retornar 106 linhas, pois o empregado 178 Kimberely Grant não tem um

DEPARTMENT_ID cadastrado e isso faz com que não haja correspondência desse atributo da tabela empregados com o mesmo atributo da tabela departamentos.

• Entretanto, às vezes precisamos fazer uma querie que nos mostre todos os valores, mesmo que não haja correspondência, para isso, devemos executar o commando SELECT com o comando JOIN conforme descrito a seguir.

(14)

Junção de Dados (Join)

O comando JOIN

• SELECT e.employee_id, e.first_name, e.last_name, e.department_id, d.department_name FROM empregados e

LEFT OUTER JOIN departamentos d

ON (e.department_id = d.department_id) ORDER BY 1;

• Essa query mostrará as 107 linhas, mesmo para aquele registro em que o atributo DEPARTMENT_ID está NULL.

(15)

Junção de Dados (Join)

Copiando Outra Tabela

• Vamos copiar uma nova tabela do usuário “HR” agora a tabela “LOCATIONS” e vamos chama-la de “LOCAIS”

CREATE TABLE locais AS

SELECT * FROM hr.locations;

• Faça um select na tabela “DEPARTAMENTOS” e veja que existe o atributo “LOCATION_ID”, observe que na tabela “LOCAIS” esse atributo também existe e existe ainda o atributo “STREET_ADDRESS”

(16)

Junção de Dados (Join)

Unindo Dados de Três Tabelas

• Vamos fazer uma query que nos mostre os atributos:

“EMPLOYEE_ID”, “FIRST_NAME”, “LAST_NAME” esses da tabela “EMPREGADOS”, os atributos “DEPARTMENT_ID” e

“DEPARTMENT_NAME” da tabela “DEPARTAMENTOS” e finalmente os atributos “LOCATION_ID” e “STREET_ADDRESS” da tabela “LOCAIS”. • Veja o select no próximo slide.

(17)

Junção de Dados (Join)

Unindo Dados de Três Tabelas

• SELECT empregados.employee_id, empregados.first_name, empregados.last_name, departamentos.department_id, departamentos.department_name, locais.location_id, locais.street_address

FROM empregados, departamentos, locais WHERE empregados.department_id =

departamentos.department_id and

(18)

Junção de Dados (Join)

Unindo Dados de Três Tabelas

• Como comentei, você precisa especificar a tabela da qual os dados serão extraídos, mas você pode simplificar isso atribuindo um alias (apelido) para a tabela, dessa forma, os apelidos irão anteceder aos atributos e na cláusula FROM você especifica qual o apelido de cada tabela.

(19)

Junção de Dados (Join)

Unindo Dados de Três Tabelas

• SELECT e.employee_id, e.first_name, e.last_name, d.department_id, d.department_name, l.location_id, l.street_address

FROM empregados e, departamentos d, locais l WHERE e.department_id = d.department_id

(20)

Junção de Dados (Join)

Unindo Dados de Três Tabelas com o comando JOIN

• SELECT employee_id, first_name, last_name, department_id, department_name, location_id, street_address FROM empregados

JOIN departamentos USING (department_id)

JOIN locais USING (location_id)

(21)

Junção de Dados (Join)

Fazendo um Auto-Referenciamento

• Faça um select na tabela “EMPREGADOS” e veja que existe a coluna “MANAGER_ID”, essa coluna indica quem é o gerente daquele

empregado.

• O gerente também é um empregado, então para você saber quem é o gerente do empregado veja qual o valor da coluna “MANAGER_ID” e em seguida procure por esse valor na coluna “EMPLOYEE_ID”, esse é o

(22)

Junção de Dados (Join)

Fazendo um Auto-Referenciamento

• Construa um SELECT que extraia as seguintes informações: do empregado: EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID do gerente: FIRST_NAME, LAST_NAME

(23)

Junção de Dados (Join)

Exemplo do resultado da query

EMPLOYEE_ID FIRST_NAME LAST_NAME MANAGER_ID FIRST_NAME_1 LAST_NAME_1 101 Neena Kochhar 100 Steven King

102 Lex De Haan 100 Steven King 103 Alexander Hunold 102 Lex De Haan 104 Bruce Ernst 103 Alexander Hunold 105 David Austin 103 Alexander Hunold 106 Valli Pataballa 103 Alexande Hunold 107 Diana Lorentz 103 Alexandr Hunold 108 Nancy Greenberg 101 Neena Kochhar 109 Daniel Faviet 108 Nancy Greenberg 110 John Chen 108 Nancy Greenberg 111 Ismael Sciarra 108 Nancy Greenberg

(24)

Junção de Dados (Join)

Resolvendo o Autoreferenciamento

SELECT a.employee_id, a.first_name, a.last_name, a.manager_id, b.first_name, b.last_name

FROM empregados a, empregados b WHERE a.manager_id = b.employee_id ORDER BY 1;

• Para resolvermos isso tivemos que utilizar duas vezes a mesma tabela na cláusula FROM.

(25)

Junção de Dados (Join)

Resolvendo o Autoreferenciamento

SELECT a.employee_id, a.first_name, a.last_name, a.manager_id, b.first_name, b.last_name FROM empregados a

INNER JOIN empregados b

ON a.manager_id = b.employee_id ORDER BY 1;

• O mesmo resultado do comando anterior mas com a utilização do comando INNER JOIN.