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
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 BDSGBD
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.productLine, 
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 realizados dos 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 plON 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
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
38
Banco de dados II – Prof Tavares
Álgebra Relacional
Exercício
 customerName, orderNumber
⋈
customers orders
 country= “USA”
40
Banco de dados II – Prof Tavares
Fim
SQL / SUBCONSULTAS