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:
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.
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;
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;
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;
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;
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.
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;
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:
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);
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.
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.
Consideremos as seguintes situações:
Situação | Modelo entidade relacionamento | WHERE | ON | USING | NATURAL JOIN |
---|---|---|---|---|---|
1 |
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 |
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 |
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 |
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 |
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 * 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 * 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 * from countries left join customer on (customer.country_id=countries.country_id);
Vamos fazer um exemplo de aplicação:
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);
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);
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);
Use a mesma sequência para detectar possíveis falhas nos outros relacionamentos.
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);
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;
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).
O primeiro query usa a sintaxe SQL99 e tem as seguintes vantagens sobre o segundo:
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.
O operador UNION permite fazer a união vertical entre os resultados de dois queries (dois conjuntos):
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';
Mas o exemplo anterior não é o melhor para ilustrar a utilidade do operador UNION, porque o query anterior pode ser substituido por um único query pois os queries parciais do exemplo anterior 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 us.user_name from users us UNION select concat(c.firstname,' ',c.surname) from customer c;
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.
Se a tabela USERS estiver vazia insira as linhas abaixo:
insert into users values (1,'Joshua Rosenthal','password'); insert into users values (2,'Steven Patton','password'); insert into users values (3,'Horacio Dalion','password'); insert into users values (4,'João Novo','password');
O operador UNION ALL permite juntar os resultados dos dois queries sem eliminar os valores repetivos, tendo por isso melhor desempenho:
select surname from customer where firstname='Chris' UNION ALL select surname from customer where firstname='Martin';
Para confirmar que existem linhas repetidas execute o query abaixo:
select t.* from ( select us.user_name from users us UNION ALL select concat(c.firstname,' ',c.surname) from customer c ) as t order by 1;
O operador INTERSECT permite fazer a intersecção vertical entre os resultados de dois queries (dois conjuntos):
O exemplo abaixo funciona em MariaDB nas versões superiores à 10.3, mas não funciona em MySQL (pelo menos até à versão 8.0.24 ainda não é suportado):
select us.user_name from users us intersect select concat(c.firstname,' ',c.surname) from customer c;
Como o operador INTERSECT vai mostrar apenas dados da primeira tabela, podemos substituir o query anterior por um INNER JOIN. Esta solução é recomendada sempre que o operador INTERSECT não esteja disponível.
select us.user_name from users us inner join customer c on us.user_name = concat(c.firstname,' ',c.surname);
Exemplo com o conjunto de tabelas HR
select sal from emp INTERSECT select salary from employees;
select e.sal from emp e inner join employees em on e.sal=em.salary;
O operador EXCEPT permite fazer a diferença (vertical) entre os resultados de dois queries. Noutros SGBDs este operador é MINUS (Oracle e Informix):
O exemplo abaixo funciona em MariaDB nas versões superiores à 10.3, mas não funciona em MySQL (pelo menos até à versão 8.0.24 ainda não é suportado):
select us.user_name from users us EXCEPT select concat(c.firstname,' ',c.surname) from customer c;
Como o operador EXCEPT vai mostrar apenas dados da primeira tabela, podemos substituir o query anterior por um LEFT OUTER JOIN em que as colunas da tabela da direita vêm com valores NULL (não há match). Esta solução é recomendada sempre que o operador EXCEPT não esteja disponível.
select us.user_name from users us left join customer c on us.user_name = concat(c.firstname,' ',c.surname) where c.firstname is null;
Exemplo com o conjunto de tabelas HR
select sal from emp EXCEPT select salary from employees;
select e.sal from emp e left outer join employees em on e.sal = em.salary where em.first_name is null;
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:
Existem dois tipos de subconsultas:
Suponha que pretende encontrar os clientes que vivem na mesma cidade que Belinda Archibald. O problema pode resolver-se em dois passos:
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:
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);
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);
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:
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)
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);
Uma subconsulta pode ser colocada em vários pontos da instrução SELECT:
Nos exemplos apresentados até aqui a subconsulta apareceu na cláusula WHERE.
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);
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 );
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;
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));
Realizado por Turtle Learning ®. Última alteração em 2011-02-26