MySQL01 > MySQL02 > MySQL03 > MySQL04

MySQL03

Numa base de dados relacional é frequente que as consultas retirem dados que estão armazenados em varias tabelas que de alguma forma se relacionam entre si. Por exemplo:

Neste módulo vamos ver a instrução SELECT de forma mais detalhada:

bthome.gifTopo

Produto cartesiano

O produto cartesiano é uma operação entre dois conjuntos na qual cada elemento do primeiro conjunto é relacionado com todos os elementos do segundo conjunto. O número de elementos do conjunto resultante é o produto entre o número de elementos de cada um dos conjuntos.

produtoCartesiano.jpg

Se considerarmos as tabelas CUSTOMER e COUNTRIES obtemos um produto cartesiano com o query abaixo:

select customer.surname, customer.firstname, countries.country 
from customer, countries;

note04.gif

Se considerarmos as tabelas WINERY e REGION obtemos um produto cartesiano com o query abaixo:

select winery.winery_name, region.region_name 
from winery, region;

note04.gif

Se considerarmos as tabelas CUSTOMER e ORDERS obtemos um produto cartesiano com o query abaixo:

select customer.surname, customer.firstname, orders.order_id, orders.date
from customer, orders;

note04.gif

Se considerarmos as tabelas WINE e WINE_TYPE obtemos um produto cartesiano com o query abaixo:

select wine.wine_name, wine_type.wine_type
from wine, wine_type;

note04.gif

bthome.gifTopo

Junção

Nas bases de dados relacionais o relacionamento entre 2 tabelas é feito com base numa condição de junção. Essa condição normalmente requer igualdade nos valores de um campo comum às duas tabelas. A junção pode ser vista como um subconjunto do produto cartesiano, pois partimos do conjunto resultante do produto cartesiano e retiramos todos os elementos que não satisfazem a condição de junção.

join.jpg

Considerando o cliente Rosenthal Joshua, como determinar o seu país? Fazendo uma consulta à tabela CUSTOMER conseguimos saber qual o seu country_id:

select surname, firstname, country_id
from customer
where surname='Rosenthal' and firstname='Joshua';

Sabendo que country_id=12 podemos obter o nome do seu país:

select country_id, country
from countries
where country_id=12;

Os dois queries anteriores podem ser fundidos num único, que faz a junção das duas tabelas. Como este query trabalha com duas tabelas devemos indicar a que tabela pertence cada coluna:

select customer.surname, customer.firstname, countries.country 
from customer, countries
where customer.country_id = countries.country_id
  and customer.surname='Rosenthal' and customer.firstname='Joshua';

A junção entre as duas tabelas CUSTOMER e COUNTRIES requer uma condição de junção descrita no exemplo abaixo:

select customer.surname, customer.firstname, countries.country 
from customer, countries
where customer.country_id = countries.country_id;

Para fazer a junção entre as tabelas WINERY e REGION devemos adicionar a condição de junção:

select winery.winery_name, region.region_name 
from winery, region
where winery.region_id = region.region_id;

Para fazer a junção entre as tabelas CUSTOMER e ORDERS devemos adicionar a condição de junção:

select customer.surname, customer.firstname, orders.order_id, orders.date
from customer, orders
where customer.cust_id = orders.cust_id;

Para fazer a junção entre as tabelas WINE e WINE_TYPE devemos adicionar a condição de junção:

select wine.wine_name, wine_type.wine_type
from wine, wine_type
where wine.wine_type = wine_type.wine_type_id;
note04.gif

bthome.gifTopo

Sintaxe SQL99 para a junção

Como foi visto no ponto anterior a junção pode ser vista como um produto cartesiano ao qual é adicionada uma condição de junção, via cláusula WHERE. Na realidade os sistemas gestores de bases de dados não resolvem a junção filtrando o produto cartesiano, pois isso implicaria a movimentação de milhares de linhas. A norma SQL99 propõe uma nova sintaxe para a junção que tem as seguintes vantagens:

Cláusula ON

A cláusula ON da sintaxe SQL99 obriga a escrever a condição de junção, o que a torna muito semelhante à utilização de WHERE.

A junção entre as tabelas CUSTOMER e COUNTRIES:

select customer.surname, customer.firstname, countries.country
from customer
inner join countries on (customer.country_id = countries.country_id);

A junção entre as tabelas WINERY e REGION:

select winery.winery_name, region.region_name
from winery
inner join region on (winery.region_id = region.region_id);

A junção entre as tabelas CUSTOMER e ORDERS:

select customer.surname, customer.firstname, orders.order_id, orders.date
from customer
inner join orders on (customer.cust_id = orders.cust_id);

A junção entre as tabelas WINE e WINE_TYPE:

select wine.wine_name, wine_type.wine_type
from wine
inner join wine_type on (wine.wine_type = wine_type.wine_type_id);

bthome.gifTopo

Cláusula USING

A cláusula USING pode ser usada em vez da cláusula ON sempre que a(s) coluna(s) usada(s) na junção tenha(m) o mesmo nome em ambas as tabelas. Esta cláusula pode ser usada mesmo que existam outras colunas com o mesmo nome em ambas as tabelas.

A junção entre as tabelas CUSTOMER e COUNTRIES:

select customer.surname, customer.firstname, countries.country
from customer
inner join countries using (country_id);

A junção entre as tabelas WINERY e REGION:

select winery.winery_name, region.region_name
from winery
inner join region using (region_id);

A junção entre as tabelas CUSTOMER e ORDERS:

select customer.surname, customer.firstname, orders.order_id, orders.date
from customer
inner join orders using (cust_id);

A junção entre as tabelas WINE e WINE_TYPE não pode ser feita com a cláusula USING porque as colunas das duas tabelas usadas na junção não têm o mesmo nome.

bthome.gifTopo

Cláusula NATURAL JOIN

A maior parte das junções baseiam-se numa condição que usa uma igualdade entre duas colunas que têm o mesmo nome nas duas tabelas. Este tipo de junção designa-se por Junção Natural (NATURAL JOIN) e tem uma cláusula específica na norma SQL99: NATURAL JOIN. Esta cláusula pode ser usada em vez da cláusula ON ou em vez da cláusula USING sempre que:

A junção natural entre as tabelas CUSTOMER e COUNTRIES:

select customer.surname, customer.firstname, countries.country 
from customer natural join countries;

A junção natural entre as tabelas WINERY e REGION:

select winery.winery_name, region.region_name 
from winery natural join region;

A junção natural entre as tabelas CUSTOMER e ORDERS:

select customer.surname, customer.firstname, orders.order_id, orders.date
from customer natural join orders;

A junção natural entre as tabelas WINE e WINE_TYPE não pode ser feita com a cláusula USING porque as colunas das duas tabelas usadas na junção não têm o mesmo nome.

note04.gif

bthome.gifTopo

Comparação entre ON, USING e NATURAL JOIN

Consideremos as seguintes situações:

Situação Modelo entidade relacionamento WHERE ON USING NATURAL JOIN
1 ex1.jpg
SELECT *
FROM t1, t2
WHERE t1.t1_c1=t2.t1_c1;
SELECT *
FROM t1 
JOIN t2 ON (t1.t1_c1=t2.t1_c1);
SELECT *
FROM t1 
JOIN t2 USING (t1_c1);
SELECT *
FROM t1 
NATURAL JOIN t2;
2 ex2.jpg
SELECT *
FROM t3, t4
WHERE t3.t3_c1=t4.t3_c1 
  AND t3.t3_c2=t4.t3_c2;
SELECT *
FROM t3 
JOIN t4 ON (t3.t3_c1=t4.t3_c1 
            AND t3.t3_c2=t4.t3_c2);
SELECT *
FROM t3 
JOIN t4 USING (t3_c1,t3_c2);
SELECT * 
FROM t3 
NATURAL JOIN t4;
3 ex3.jpg
SELECT *
FROM t5, t6
WHERE t5.t5_c1=t6.t6_c2;
SELECT *
FROM t5 
JOIN t6 ON (t5.t5_c1=t6.t6_c2);
Não é possível Não é possível
4 ex4.jpg
SELECT *
FROM t7, t8
WHERE t7.t7_c1=t8.t7_c1;
SELECT *
FROM t7
JOIN t8 ON (t7.t7_c1=t8.t7_c1);
SELECT *
FROM t7
JOIN t8 USING (t7_c1);
Não é possível

bthome.gifTopo

Junção externa

A junção entre as tabelas CUSTOMER e COUNTRIES vista nos exemplos anteriores devolve as linhas de CUSTOMER que se relacionam com uma linha de COUNTRIES:

select customer.surname, customer.firstname, countries.country 
from customer 
inner join countries on (customer.country_id=countries.country_id);

Este tipo de junção designa-se por interna e é a mais usual. No entanto, em algumas situações é necessário fazer uma junção externa. Esta alarga o conceito anterior pois mostra os paises que não se relacionam com clientes ou os clientes que não têm país.

No exemplo abaixo temos uma junção externa à direita, o que significa que aparecerão todas as linhas da tabela do lado direito, mesmo que não tenham relacionamento com alguma linha da tabela do lado esquerdo. No nosso caso isto significa que aparecerão todos os países, mesmo aqueles que não têm clientes associados:

select customer.surname, customer.firstname, countries.country
from customer
right join countries on (customer.country_id=countries.country_id);

A junção externa à esquerda tem o mesmo significado aplicado à tabela do lado esquerdo da junção:

select customer.surname, customer.firstname, countries.country
from customer
left join countries on (customer.country_id=countries.country_id);

Vamos fazer um exemplo de aplicação:

  1. Fazer a junção interna entre ORDERS e ITEMS:
    select orders.order_id, orders.cust_id, items.order_id, items.cust_id, items.item_id
    from orders
    inner join items on (orders.order_id = items.order_id and orders.cust_id = items.cust_id);
    
  2. Quantas linhas devolveu? 7706
  3. Fazer a junção externa à esquerda entre ORDERS e ITEMS:
    select orders.order_id, orders.cust_id, items.order_id, items.cust_id, items.item_id
    from orders
    left join items on (orders.order_id = items.order_id and orders.cust_id = items.cust_id);
    
  4. Quantas linhas devolveu? 7706
  5. Fazer a junção externa à direita entre ORDERS e ITEMS:
    select orders.order_id, orders.cust_id, items.order_id, items.cust_id, items.item_id
    from orders
    right join items on (orders.order_id = items.order_id and orders.cust_id = items.cust_id);
    
  6. Quantas linhas devolveu? 7780
  7. Isto significa que há ITEMS que não têm uma ORDER correspondente e portanto a base de dados não está a forçar a integridade do modelo !!!!

Use a mesma sequência para detectar possíveis falhas nos outros relacionamentos.

bthome.gifTopo

Operador UNION

O operador UNION permite fazer a união vertical entre os resultados de dois queries (dois conjuntos): uniao.gif

Considere os seguintes queries:

select surname
from customer
where firstname='Chris';
select surname
from customer
where firstname='Martin';

O operador UNION pode ser usado para juntar o resultado:

select surname
from customer
where firstname='Chris'
UNION
select surname
from customer
where firstname='Martin';

O número de linhas resultante do UNION é inferior à soma das linhas dos queries executados individualmente, porque este operador elimina as linhas repetidas, como é descrito pela teoria dos conjuntos.

Neste caso os dois queries unidos pelo operador UNION poderiam ser substituídos por um único query com duas condições, unidas pelo operador lógico OR, pois os dois queries consultam a mesma tabela. Para eliminar as linhas repetidas recorremos à cláusula DISTINCT:

select distinct surname
from customer
where firstname='Chris' OR  firstname='Martin';
select distinct surname
from customer
where firstname in ('Chris', 'Martin');

Há situações em que o UNION não pode ser substituído por OR. Por exemplo, o operador UNION permite juntar colunas provenientes de tabelas diferentes, desde que tenham o mesmo tipo de dados:

select surname
from customer
union
select user_name
from users;

bthome.gifTopo

Sinónimos para nomes de tabelas

O nome da tabela pode ser substituído por um sinónimo, como mostram os exemplos abaixo:

A junção entre as tabelas CUSTOMER e COUNTRIES:

select cust.surname, cust.firstname, coun.country
from customer cust
inner join countries coun on (cust.country_id = coun.country_id);

A junção entre as tabelas WINERY e REGION:

select w.winery_name, r.region_name
from winery w
inner join region r on (w.region_id = r.region_id);

A junção entre as tabelas CUSTOMER e ORDERS:

select c.surname, c.firstname, o.order_id, o.date
from customer c
inner join orders o on (c.cust_id = o.cust_id);

A junção entre as tabelas WINE e WINE_TYPE:

select a.wine_name, b.wine_type
from wine a
inner join wine_type b on (a.wine_type = b.wine_type_id);

bthome.gifTopo

Junção com mais de duas tabelas

A junção pode ser feita com mais de duas tabelas. Para isso o sistema gestor de base de dados começa por juntar duas tabelas, produzindo uma nova tabela temporária, que possui a soma das colunas das tabelas iniciais. Essa nova tabela será junta com a próxima, formando uma nova tabela temporária que recebe mais colunas.

O query abaixo determina quantas garrafas de cada vinho foram pedidas em cada encomenda:

select c.surname, c.firstname, o.order_id, o.date, w.wine_name, i.qty, i.price
from customer c
inner join orders o on (c.cust_id=o.cust_id)
inner join items i on (o.cust_id=i.cust_id and o.order_id=i.order_id)
inner join wine w on (i.wine_id=w.wine_id);

O query abaixo mostra os tipos de uva que formam cada vinho:

select a.wine_id, a.wine_name, c.winery_name, a.year, a.description, b.id, g.variety
from wine a
inner join wine_variety b on (a.wine_id=b.wine_id)
inner join grape_variety g on (b.variety_id=g.variety_id)
inner join winery c on (a.winery_id=c.winery_id)
order by a.wine_name, a.year, c.winery_name, b.id;

bthome.gifTopo

A cláusula de junção e a cláusula de filtro

O query abaixo determina todas as encomendas feitas pelo cliente Rosenthal Joshua:

select c.surname, c.firstname, o.order_id, o.date, w.wine_name, i.qty, i.price
from customer c
inner join orders o on (c.cust_id=o.cust_id)
inner join items i on (o.cust_id=i.cust_id and o.order_id=i.order_id)
inner join wine w on (i.wine_id=w.wine_id)
where c.surname='Rosenthal' and firstname='Joshua';

O mesmo query pode ser escrito usando apenas a cláusula WHERE:

select c.surname, c.firstname, o.order_id, o.date, w.wine_name, i.qty, i.price
from customer c, orders o, items i, wine w
where c.cust_id=o.cust_id
  and o.cust_id=i.cust_id and o.order_id=i.order_id
  and i.wine_id=w.wine_id
  and c.surname='Rosenthal' and firstname='Joshua';

Como se pode ver no segundo exemplo, na cláusula WHERE algumas condições são usadas para a junção entre as tabelas e outra é usada para escolher as linhas que nos interessam (filtro).

note04.gif O primeiro query usa a sintaxe SQL99 e tem as seguintes vantagens sobre o segundo:

bthome.gifTopo

A junção feita com coluna(s) UNIQUE

As boas práticas recomendam que a junção entre duas tabelas seja feita entre a chave primária (PK - Primary Key) de uma e a chave estrangeira (FK - Foreign Key) da outra tabela. Os motores relacionais dos diferentes fabricantes permitem que a junção seja feita não pela chave primária mas por uma chave alternativa, que por definição de chave, deve ter valores únicos. Por exemplo, em vez do número de cliente poderíamos usar o NIF (Número de Identificação Fiscal), desde que este seja único para cada empregado.

bthome.gifTopo

Subconsultas

Uma subconsulta é uma instrução SELECT que está encadeada dentro de outra instrução SELECT. A utilização de subconsultas permite construir comandos potentes a partir de comandos mais simples.

Na construção de subconsultas devem ser seguidas as seguintes linhas de orientação:

bthome.gifTopo

Subconsulta simples e correlacionada

Existem dois tipos de subconsultas:

bthome.gifTopo

Subconsulta simples

Suponha que pretende encontrar os clientes que vivem na mesma cidade que Belinda Archibald. O problema pode resolver-se em dois passos:

  1. Encontrar a cidade em que vive Belinda Archibald;
  2. Encontrar o(s) cliente(s) que vivem nessa cidade;

O primeiro query:

select city
from customer
where surname='Archibald' and firstname='Belinda';

O segundo query:

select *
from customer
where city='COONAWARRA';

Os dois queries podem ser fundidos num único:

select *
from customer
where city = (select city
              from customer
              where surname='Archibald' and firstname='Belinda');

A consulta interior é executada em primeiro lugar produzindo um resultado ('COONAWARRA'). O bloco externo é depois executado, utilizando o valor devolvido pela consulta interna para completar a sua condição de pesquisa.

Suponha que pretende encontrar os clientes que nasceram no mesmo dia que Belinda Archibald. O problema pode resolver-se em dois passos:

  1. Encontrar a data de nascimento de Belinda Archibald;
  2. Encontrar o(s) cliente(s) que nasceram na mesma data;

O primeiro query:

select birth_date
from customer
where surname='Archibald' and firstname='Belinda';

O segundo query:

select * 
from customer 
where birth_date = '1966-08-11';

Os dois queries podem ser fundidos num único:

select *
from customer
where birth_date = (select birth_date
                    from customer
                    where surname='Archibald' and firstname='Belinda');

Encontrar os clientes que compraram o vinho mais caro:

select c.surname, c.firstname, o.order_id, o.date, w.wine_name, i.qty, i.price
from customer c
inner join orders o on (c.cust_id=o.cust_id)
inner join items i on (o.cust_id=i.cust_id and o.order_id=i.order_id)
inner join wine w on (i.wine_id=w.wine_id)
where i.price = (select max(x.price) from items x);

bthome.gifTopo

Subconsulta correlacionada

Uma subconsulta correlacionada é executada de forma diferente da subconsulta simples. Nestas consultas o subquery precisa de um dado que vem do query principal, pelo que o SELECT interno é executado tantas vezes quantas as linhas que são processadas no query principal.

A lista abaixo contem os passos necessários para executar uma consulta correlacionada:

Vamos procurar as regiões que têm mais que 35 adegas (wineries):

select r.region_name
from region r
where 35 < (select count(*)
            from winery w
            where r.region_id = w.region_id);

bthome.gifTopo

Subconsulta que devolve várias linhas - operadores do SQL

Os exemplos de subconsulta que vimos até agora devolvem apenas uma linha. Mas uma subconsulta pode devolver várias linhas, o que obriga a consulta externa a ter cuidados especiais e a recorrer aos operadores do SQL:

bthome.gifTopo

IN

Quando a subconsulta devolve várias linhas o operador IN pode ser usado para validar se uma linha da consulta externa está presente no conjunto criado pela subconsulta. Devolve TRUE se o valor usado na consulta externa está incluído no conjunto devolvido pela consulta interna. Este operador pode ser negado com NOT

No ponto Consulta Correlacionada escrevemos um query para procurar as regiões que têm mais que 35 adegas (wineries). Vamos agora ver uma solução alternativa que usa IN:

select r.region_name
from region r
where r.region_id in (select w.region_id
                      from winery w
                      group by w.region_id
                      having count(*) > 35);

O query abaixo pretende terminar quais os vinhos que foram comprados pelos clientes mais frequentes. Consideramos clientes mais frequentes aqueles que fizeram mais de 5 pedidos:

select distinct w.wine_name
from wine w
inner join items i on (w.wine_id=i.wine_id)
where (i.cust_id) in (select a.cust_id
                      from customer a
                      inner join orders b on (a.cust_id=b.cust_id)
                      group by b.cust_id
                      having count(*) > 5)

bthome.gifTopo

EXISTS

O operador EXISTS permite à consulta externa verificar se a consulta interna devolveu pelo menos uma linha. Não se preocupa com o valor das linhas, mas sim com a cardinalidade do conjunto resultado, ou seja, com o número de elementos do conjunto resultado. Devolve TRUE se a cardinalidade for superior a 0 (zero) e FALSE caso seja igual a 0 (zero). Este operador pode ser negado com NOT.

No ponto Consulta Correlacionada escrevemos um query para procurar as regiões que têm mais que 35 adegas (wineries). No ponto anterior escrevemos uma segunda solução para o problema. Agora vamos ver outra alternativa que usa o operador EXISTS:

select r.region_name
from region r
where exists (select count(*)
              from winery w
              where r.region_id = w.region_id
              group by w.region_id
              having count(*) > 35);

bthome.gifTopo

Subconsulta e cláusulas da instrução SELECT

Uma subconsulta pode ser colocada em vários pontos da instrução SELECT:

bthome.gifTopo

WHERE

Nos exemplos apresentados até aqui a subconsulta apareceu na cláusula WHERE.

bthome.gifTopo

HAVING

Uma subconsulta pode ser colocada na cláusula HAVING. O exemplo abaixo determina os vinhos cujo preço médio de venda foi superior ao preço de venda médio do vinho número 255:

select w.wine_id, w.wine_name, avg(i.price)
from wine w
inner join items i on (w.wine_id=i.wine_id)
group by wine_id
having avg(i.price) > (select avg(a.price)
                       from items a
                       where a.wine_id = 255);

bthome.gifTopo

FROM

Uma subconsulta pode ser colocada na cláusula FROM, o que é o mesmo que criar uma tabela virtual. O exemplo abaixo mostra os nomes e as datas de aniversário dos clientes com identificador 1 e 2. Ao colocarmos as subconsultas na clásula FROM estamos a produzir duas tabelas temporárias, cada uma delas com 3 colunas e apenas uma linha. O query provoca o produto cartesiano entre duas tabelas que possuem apenas 1 linha, pelo que o resultado tem uma linha. Na prática este query coloca o resultado de dois queries na mesma linha:

SELECT * 
FROM (
      (SELECT firstname,surname,birth_date FROM customer WHERE cust_id=1) t1,
      (SELECT firstname,surname,birth_date FROM customer WHERE cust_id=2) t2
);

bthome.gifTopo

SELECT

As subconsultas podem ser colocadas na cláusula SELECT. O exemplo abaixo coloca lado a lado o maior preço de venda e o maior preço de custo do vinho número 19:

select
   (select max(price) from items where wine_id=19) as MAX,
   (select max(cost)  from inventory where wine_id=19) as MIN
from
   (select 1) t1;

bthome.gifTopo

Subconsulta e encadeamento múltiplo

Uma subconsulta pode receber outra, pelo que as subconsultas podem ser encadeadas. O exemplo abaixo determina a região que fabrica o vinho número 17:

select region_name
from region
where region_id = (select region_id
                   from winery
                   where winery_id = (select winery_id
                                      from wine
                                      where wine_id=17));

bthome.gifTopo


Realizado por Turtle Learning ®. Última alteração em 2011-02-26