MySQL Consulta Join Union

Prévia do material em texto

1 
Banco de dados II – Prof Tavares 
Banco de Dados II 
SQL 
Instruções Joins / Union / 
Subconsultas 
Banco de Dados I – Prof Tavares 
Requisitos 
de Dados 
Projeto Conceitual 
Projeto Lógico 
Projeto Físico 
Coleta/Especificação 
de Requisitos 
Requisitos 
Funcionais 
Análise Funcional 
Projeto Funcional 
Programação 
Programa de 
aplicação de BD SGBD 
Programa de 
aplicação de BD 
Programa de 
aplicação 
dados 
e 
regras 
Mundo 
Real 
3 
Banco de dados II – Prof Tavares 
SQL 
•Definição de dados (DDL) - permite ao usuário a 
definição da estrutura e organização dos dados 
armazenados, e as relações que existem entre eles; 
•Manipulação de dados (DML) - permite ao usuário ou 
a um programa de aplicação, a inclusão, remoção, 
seleção ou atualização de dados previamente 
armazenados no banco; 
4 
Banco de dados II – Prof Tavares 
5 
Banco de dados II – Prof Tavares 
• As chaves são usadas para garantir integridade num banco de 
dados. Após sua definição, o próprio SGBD se encarrega de 
fazer as validações. 
• A chave primária (PK) define um identificador único para cada 
tupla. Seu uso garante que não haverão 2 registros com valores 
repetidos na tabela. Só é possível criar uma chave primária por 
tabela. É possível criar chaves primárias compostas por 2 ou 
mais campos. 
• A chave estrangeira (FK) garante que a informação inserida no 
campo seja consistente. Ou seja, não é permitido gravar um 
valor na chave estrangeira se esse valor não existir na tabela 
referenciada. Uma tabela pode ter diversas chaves estrangeiras, 
cada uma referenciando sua tabela de origem. 
Chaves Primária e Estrangeira 
6 
Banco de dados II – Prof Tavares 
Recuperando Dados de Várias Tabelas (JOINS) 
Necessidade de acessar simultaneamente várias tabelas 
relacionadas entre si. 
 
Qualificadores de Nome 
• Qualificador de nome consiste no nome da tabela seguido 
de um ponto e o nome da coluna na tabela, por exemplo: 
 PRODUTO.descricao 
• Os qualificadores de nome são utilizados em uma consulta 
para efetivar a junção (JOIN) entre tabelas, uma vez que o 
relacionamento entre tabelas é realizado por meio de 
chaves estrangeiras. 
SQL 
JOIN / UNION / Subconsultas 
7 
Banco de dados II – Prof Tavares 
JOIN via Cláusula WHERE 
• Não recomendada. 
• Consulta: Recuperar os nome de todos os produtos com preço 
menor que $100 e as respectivas descrições das linhas de 
produtos. 
 
SELECT products.productName, products.productName, 
 productlines.textDescription 
 FROM products, productlines 
WHERE products.productLine = productlines.productLine 
 AND products.buyPrice < 100 
SQL 
JOIN / UNION / Subconsultas 
A equação apresentada na 
cláusula WHERE é chamada 
de EQUAÇÃO DE JUNÇÃO. 
8 
Banco de dados II – Prof Tavares 
ALIAS (Apelido) nas Tabelas 
 
SELECT p.productName, pl.productLine, 
 pl.textDescription 
 FROM products p , productlines pl 
WHERE p.productLine = pl.productLine 
 AND p.buyPrice < 100 
SQL 
JOIN / UNION / Subconsultas 
9 
Banco de dados II – Prof Tavares 
Álgebra Relacional 
• A Álgebra Relacional é uma linguagem de 
consulta procedural que agrega um conjunto de 
operações sobre modelos relacionais de dados. 
• Operações: 
–Seleção, Projeção, Junção, União, Intersecção, 
Diferença, Produto Cartesiano, Divisão e Renomear. 
10 
Banco de dados II – Prof Tavares 
Representação Gráfica 
Seleção Projeção Junção 
União Intersecção Diferença 
11 
Banco de dados II – Prof Tavares 
Relações de Dados 
Nome_agencia Nro_emprestimo Total 
Timbo 17 1000 
Indaial 23 2000 
Blumenau 15 1500 
Pomerode 93 500 
Gaspar 11 900 
Blumenau 16 1300 
Nome_Cliente Nro_emprestimo 
Jonas 17 
Silvio 23 
Henrique 15 
Carlos 93 
Silvio 11 
William 17 
Adalberto 16 
emprestimos devedores 
Nome_Cliente Nro_conta 
Jonas 11111 
Silvio 22222 
Henrique 33333 
Jackson 44444 
contas 
12 
Banco de dados II – Prof Tavares 
Álgebra Relacional 
Operação de Junção 
• A operação de join é uma operação binária que permite 
combinar certas relações e um produto cartesiano 
dentro de uma operação. 
Ex.  nome_cliente, nro_emprestimo, total (emprestimo ⋈ devedores) 
SELECT nome_cliente, nro_emprestimo, total 
FROM emprestimo, devedores 
WHERE emprestimo.nro_emprestimo = devedores.nro_emprestimo 
Nome_Cliente Nro_emprestimo Total 
Jonas 17 1000 
Silvio 23 2000 
Henrique 15 1500 
Carlos 93 500 
Silvio 11 900 
William 17 1000 
Adalberto 16 1300 
13 
Banco de dados II – Prof Tavares 
Operação de Junção 
Ex.  nome_cliente, nro_emprestimo, total 
(nome_agencia = “Blumenau” 
 (emprestimo ⋈ devedores)) 
 SELECT nome_cliente, nro_emprestimo, total 
 FROM emprestimo, devedores 
 WHERE emprestimo.nro_emprestimo = devedores.nro_emprestimo 
 and nome_agencia = ‘Blumenau’ 
Nome_Cliente Nro_emprestimo Total 
Henrique 15 1500 
Adalberto 16 1300 
14 
Banco de dados II – Prof Tavares 
Árvore de Expressões 
otimização 
 nome_cliente, nro_emprestimo, total 
 
 
 ⋈ 
 
 
 
emprestimo devedores 
nome_agencia = “Blumenau” 
15 
Banco de dados II – Prof Tavares 
TIPO DE JOIN 
• Junção Estilo ANSI: JOIN ... ON ... 
• JOIN como tipo de join, serão incluídas somente as linhas que 
satisfazem a condição do join. 
• OUTER JOIN, incluímos as linhas que satisfazem a condição 
de JOIN e as linhas restantes de uma das tabelas do JOIN. 
SQL 
JOIN / UNION / Subconsultas 
16 
Banco de dados II – Prof Tavares 
Comparação entre os tipos de JOIN 
• Não é possível afirmar que um tipo de instrução JOIN seja 
melhor que outro. 
• É preciso analisar qual a informação desejada e utilizar a 
instrução adequada. 
SQL 
JOIN / UNION / Subconsultas 
outer join 
17 
Banco de dados II – Prof Tavares 
Junção Estilo ANSI: JOIN ... ON ... 
 
 SELECT p.productName, pl.productLine, 
 pl.textDescription 
 FROM products p JOIN productlines pl 
 ON p.productLine = pl.productLine 
 WHERE p.buyPrice < 100 
SQL 
JOIN / UNION / Subconsultas 
18 
Banco de dados II – Prof Tavares 
Junção Estilo ANSI: JOIN ... ON ... 
 
 SELECT p.productName, pl.productLine, 
 pl.textDescription 
 FROM products p INNER JOIN productlines pl 
 ON p.productLine = pl.productLine 
 WHERE p.buyPrice < 100 
SQL 
JOIN / UNION / Subconsultas 
19 
Banco de dados II – Prof Tavares 
Junção Estilo ANSI: JOIN ... ON ... 
 
 SELECT p.productName, pl.productLine, 
 pl.textDescription 
 FROM products p CROSS JOIN productlines pl 
 ON p.productLine = pl.productLine 
 WHERE p.buyPrice < 100 
SQL 
JOIN / UNION / Subconsultas 
20 
Banco de dados II – Prof Tavares 
Junção Estilo ANSI: JOIN ... ON ... 
 
 SELECT p.productName, pl.productLine, 
 pl.textDescription 
 FROM products p JOIN productlines pl 
 USING(productLine) 
 WHERE p.buyPrice < 100 
SQL 
JOIN / UNION / Subconsultas 
Colunas de 
junção com 
mesmo 
nome 
21 
Banco de dados II – Prof Tavares 
JOIN com mais de duas tabelas 
• Consulta: Recuperar os nome de todos os produtos com preço 
menor que $100, as respectivas descrições das linhas de produtos 
e os detalhes dos pedidos realizadosdos referidos produtos. 
 
SELECT p.productName, pl.productLine, 
 pl.textDescription, od.orderNumber, od.quantityOrdered 
 FROM products p 
 JOIN productlines pl ON p.productLine = pl.productLine 
 JOIN orderdetails od ON p.productCode = od.productCode 
 WHERE p.buyPrice < 100 
SQL 
JOIN / UNION / Subconsultas 
22 
Banco de dados II – Prof Tavares 
• Consulta: Listar as linhas de produtos, as descrições das linhas de 
produtos e a quantidade de produtos por linha de produtos. 
 SELECT pl.productLine, pl.textDescription, 
 count(pl.productLine) 
 FROM products p 
 JOIN productlines pl 
 ON p.productLine = pl.productLine 
GROUP BY pl.productLine, pl.textDescription 
SQL 
JOIN / UNION / Subconsultas 
23 
Banco de dados II – Prof Tavares 
• Consulta: Listar o nome completo dos empregados vendedores e as 
respectivas quantidades de clientes . 
 SELECT concat(e.firstName, " ", e.lastName) as Nome, 
 count(*) as Qte 
 FROM employees e 
 JOIN customers c 
 ON e.employeeNumber = c.salesRepEmployeeNumber 
GROUP BY Nome 
SQL 
JOIN / UNION / Subconsultas 
24 
Banco de dados II – Prof Tavares 
OUTER JOINS 
• É a seleção em que são restritas as linhas que interessam em uma 
tabela, mas são consideradas todas as linhas de outra tabela. 
• Ou seja, queremos ver quais linhas de uma tabela estão 
relacionadas com a outra tabela e quais linhas não estão. 
• Possibilita verificar chaves primárias e chaves estrangeiras sem 
sincronia ou simetria. 
• Um OUTER JOIN somente pode ser realizado entre duas tabelas. 
SQL 
JOIN / UNION / Subconsultas 
25 
Banco de dados II – Prof Tavares 
OUTER JOINS 
LEFT JOIN - São incluídas todas as linhas da tabela do primeiro 
nome de tabela ( a tabela mais à esquerda da expressão). 
RIGHT JOIN - São incluídas todas as linhas da tabela do 
segundo nome de tabela da expressão (tabelas mais à direita da 
expressão). 
SQL 
JOIN / UNION / Subconsultas 
26 
Banco de dados II – Prof Tavares 
Cláusula LEFT [OUTER] JOIN 
Consulta: Recuperar o nome dos empregados e os nomes dos 
respectivos clientes. Incluir os nomes dos funcionários que não 
atendem clientes. 
 
 SELECT concat(e.firstName," ",e.lastName) as empregado, 
 c.customerName as cliente 
 FROM employees e LEFT JOIN customers c 
 ON e.employeeNumber = c.salesRepEmployeeNumber 
ORDER BY e.firstName 
SQL 
JOIN / UNION / Subconsultas 
27 
Banco de dados II – Prof Tavares 
Cláusula RIGHT [OUTER] JOIN 
Consulta: Recuperar o nome dos empregados e os nomes dos 
respectivos clientes. Incluir os nomes dos clientes não atendidos por 
funcionários. 
 
 SELECT concat(e.firstName," ",e.lastName) as empregado, 
 c.customerName as cliente 
 FROM employees e RIGHT JOIN customers c 
 ON e.employeeNumber = c.salesRepEmployeeNumber 
ORDER BY e.firstName 
SQL 
JOIN / UNION / Subconsultas 
28 
Banco de dados II – Prof Tavares 
CROSS JOIN OU PRODUTO CARTESIANO 
 
SELECT pr.productName, pl.productLine 
 FROM products pr CROSS JOIN productlines pl 
SQL 
JOIN / UNION / Subconsultas 
29 
Banco de dados II – Prof Tavares 
JUNTANDO MAIS DE DUAS TABELAS 
Consulta: Listar os números de pedidos, com respectivos 
nomes de produtos, linha de produto e quantidades. 
 
SELECT od.orderNumber, pr.productName, 
 pl.productLine, od.quantityOrdered 
 FROM products pr 
 JOIN productlines pl USING(productLine) 
 JOIN orderdetails od USING(productCode) 
SQL 
JOIN / UNION / Subconsultas 
30 
Banco de dados II – Prof Tavares 
Álgebra Relacional 
• A Álgebra Relacional é uma linguagem de 
consulta procedural que agrega um conjunto de 
operações sobre modelos relacionais de dados. 
• Operações: 
–Seleção, Projeção, Junção, União, Intersecção, 
Diferença, Produto Cartesiano, Divisão e Renomear. 
31 
Banco de dados II – Prof Tavares 
Representação Gráfica 
Seleção Projeção Junção 
União Intersecção Diferença 
32 
Banco de dados II – Prof Tavares 
Relações de Dados 
Nome_agencia Nro_emprestimo Total 
Timbo 17 1000 
Indaial 23 2000 
Blumenau 15 1500 
Pomerode 93 500 
Gaspar 11 900 
Blumenau 16 1300 
Nome_Cliente Nro_emprestimo 
Jonas 17 
Silvio 23 
Henrique 15 
Carlos 93 
Silvio 11 
William 17 
Adalberto 16 
emprestimos devedores 
Nome_Cliente Nro_conta 
Jonas 11111 
Silvio 22222 
Henrique 33333 
Jackson 44444 
contas 
33 
Banco de dados II – Prof Tavares 
Álgebra Relacional 
Operação de União 
• A operação de union é uma operação binária que 
permite unir dois conjuntos ou relações de dados. 
• Nesta operação, os conjuntos duplicados são 
eliminados. 
Ex.  nome_cliente(devedores)   nome_cliente(contas) 
 
Nome_Cliente 
Adalberto 
Carlos 
Henrique 
Jackson 
Jonas 
Silvio 
William 
SELECT nome_cliente 
 FROM devedores 
UNION 
SELECT nome_cliente 
 FROM contas 
 
34 
Banco de dados II – Prof Tavares 
• As instruções UNION e UNION ALL servem para gerar como 
resultado a união de 2 ou mais consultas. 
• As consultas podem usar dados das mesmas tabelas ou 
obter os dados de diferentes fontes, desde que a quantidade 
(e o tipo) de colunas das consultas sejam iguais. 
• A diferença entre UNION e UNION ALL é que o comando 
UNION ignora registros repetidos. Em contrapartida, a 
instrução UNION ALL é mais rápida. 
SQL UNION 
35 
Banco de dados II – Prof Tavares 
Consulta: Listar todos os empregados e clientes. 
 
(SELECT customerName as Nome FROM customers) 
UNION 
(SELECT concat(firstName," ",lastName) as nome FROM employees) 
ORDER BY nome 
 
(SELECT customerName as nome FROM customers) 
UNION ALL 
(SELECT concat(firstName," ",firstName) as Nome FROM 
employees)ORDER BY nome 
SQL UNION 
36 
Banco de dados II – Prof Tavares 
Implementação do FULL JOIN 
Consulta: Recuperar o nome dos empregados e os nomes dos respectivos 
clientes. Incluir os nomes dos clientes não atendidos por funcionários e vice-
versa. 
 (SELECT concat(e.firstName," ",e.lastName) as empregado, 
 c.customerName as cliente 
 FROM employees e RIGHT JOIN customers c ON 
 e.employeeNumber 
 = c.salesRepEmployeeNumber) 
 UNION 
 (SELECT concat(e.firstName," ",e.lastName) as empregado, 
 c.customerName as cliente 
 FROM employees e LEFT JOIN customers c ON 
 e.employeeNumber 
 = c.salesRepEmployeeNumber) 
ORDER BY empregado 
SQL 
JOIN / UNION / Subconsultas 
37 
Banco de dados II – Prof Tavares 
Uma subconsulta é uma consulta interna (nível inferior) cujo 
conjunto resultado é passado como argumento para uma 
consulta externa (nível superior). 
Não é permitido utilizar a cláusula ORDER BY na subconsulta. 
É permitido utilizar a cláusula ORDER BY na consulta de nível 
superior. 
Uma tabela pode correlacionar-se consigo mesma para 
devolver um conjunto resultado adequado. 
 
SQL / SUBCONSULTAS 
38 
Banco de dados II – Prof Tavares 
Consulta: Quantos clientes não realizaram pedidos (orders)? 
 
SELECT count(*) as 'Quantidade de clientes que não realizaram pedidos' 
 FROM CUSTOMERS 
 WHERE customerNumber not in 
( 
 SELECT distinct customerNumberfrom orders 
) 
SQL / SUBCONSULTAS 
39 
Banco de dados II – Prof Tavares 
Consulta: Listar os números dos pedidos, os nomes dos 
produtos e as respectivas quantidades. Restrição: quantidades 
pedidas abaixo da média. 
 
SELECT od.orderNumber, pr.productName, od.quantityOrdered 
 FROM products pr join orderdetails od using(productCode) 
 WHERE od.quantityOrdered < 
 ( 
 select avg(quantityOrdered) from orderdetails 
 ) 
SQL / SUBCONSULTAS 
40 
Banco de dados II – Prof Tavares 
Consulta: Listar os clientes que realizaram mais do que 5 
pagamentos. 
SELECT * 
 FROM customers 
WHERE customerNumber in 
( 
select customerNumber 
from payments 
group by customerNumber 
having count(*)> 5 
) 
SQL / SUBCONSULTAS 
41 
Banco de dados II – Prof Tavares 
SQL / SUBCONSULTAS 
1) Listar os pagamentos realizados acima da média das quantias pagas. 
42 
Banco de dados II – Prof Tavares 
SQL / SUBCONSULTAS 
2) Listar os empregados que trabalham no mesmo escritório 
(officeCode) do presidente (job_title = president ). 
43 
Banco de dados II – Prof Tavares 
Álgebra Relacional 
Exercício 
 customerName, orderNumber 
 
 
 ⋈ 
 
 
 
customers orders 
 country= “USA” 
45 
Banco de dados II – Prof Tavares 
Fim 
SQL / SUBCONSULTAS