![]() |
MC536 - Consultas complexas com Exists, not ExistsProf. Célio Guimarães IC - Unicamp |
![]() |
Referência: livro-texto, pp 146-150.
Os operadores exists, not exists do Cálculo Relacional de Tuplas
foram introduzidos no padrão SQL/92. Eles permitem resolver consultas
complexas como a "consulta clássica e a super-clássica", através de consultas correlacionadas.
A sintaxe para seu uso é:
Vejamos agora a solução da consulta clássica "obter os jogadores que ganharam
todos os torneios do Grand Slam".
A abordagem é a mesma vista com a Álgebra Relacional: "obter inicialmente os
jogadores que não ganharam algum desses torneios".
O exemplo a seguir ilustra a técnica para resolver problemas ainda
mais complexos, usando consultas correlacionadas:
Esta consulta requer a igualdade de dois conjuntos, o que pode ser
feito verificando que o primeiro contém o segundo e vice versa.
Exercício 2 (consulta super-clássica):
Exercicio 3
Exercício 4:
Exercício 5:
Exercício 6
Exercício 7 (Consulta super-clássica):
select ...
from ...
where [not] exists
(subconsulta)
A cláusula where exists retorna verdadeiro se a subconsulta
produz um resultado não vazio (uma tabela, linha ou valor) e falso caso contrário;
where not exists faz o contrário,
retorna verdadeiro se a subconsulta dá um resultado vazio.
A título de ilustração, fixe um jogador, digamos "numj1" e obtenha os torneios
que ele não venceu (nota: nossa tabela torneios só possui os torneios do Grand Slam):
select numt
from torneios
where numt not in
(select numt
from torneios
where numj = numj1)
Observe que a subconsulta exibe os torneios que numj1 venceu.
Agora queremos obter todos os jogadores na mesma situação de "numj1".
Para este fim usaremos uma "consulta correlacionada":
select distinct t1.numj
from torneios t1
where exists
(select numt
from torneios
where numt not in
(select numt
from torneios
where numj = t1.numj)
)
Se em vez de where exists usarmos where not exists
obteremos a solução da consulta clássica: "exiba os números dos jogadores que ganharam
todos os torneios".
Pelo fato de ser uma consulta correlacionada (um laço dentro de outro),
esta solução não é tão eficiente (nem tão clara) quanto à vista da
tradução da Álgebra Relacional
para SQL, mas a abordagem do problema é a mesma: "encontrar primeiro
jogadores que não ganharam algum torneio (ou fornecedores que não fornecem
algum material, no caso da tabela FM)".
"(*)Dê o número de cada jogador que venceu pelo menos todos os torneios que
o jogador 817 venceu".
Observe que esta consulta procura encontrar um conjunto (no caso,
uma tabela contendo os torneios que o jogador 817 venceu) dentro de outro e a
solução usa a seguinte identidade da teoria elementar de conjuntos:
Se S ⊆ T então S - T = ∅ (ver
livro-texto pp. 175-176)
Um esqueleto de consulta SQL para fazer o teste acima seria:
select ...
from ...
where not exists
(S - T)
e lembrando que em mysql a subtração seria feita através de uma subconsulta:
select ...
from ...
where not exists
(S)
and ... not in
(T)
A fim de ilustrar a técnica que também será usada nos exemplos seguintes,
vamos apresentar passo a passo a solução do problema (*) acima:
select j.numj
from jogadores j
where j.numj <> 817
and not exists
(select numt
from torneios
where numj = 817
and numt not in
(select numt
from torneios
where numj = j.numj))
Exercício 1:
"Dê o número de cada jogador que venceu exatamente os mesmos torneios
que o jogador 817".
A consulta (*) pode ser facilmente estendida
para a seguinte consulta (super-clássica):
"(**) Obtenha pares de jogadores (j1, j2) onde j1 ganhou todos os torneios
que j2 ganhou".
Solução:
select distinct t1.numj, t2.numj
from torneios t1, torneios t2
where t1.numj <> t2.numj
and not exists
(select numt from torneios
where numj = t2.numj
and numt not in
(select numt from torneios
where numj = t1.numj))
order by t1.numj, t2.numj
Verifique a correção da solução analizando o conteúdo da tabela torneios
ordenado pelo número do jogador e número do torneio.
Observe que os recursos de contagem do SQL não servem para resolver este problema.
Usando a tabela FM(f,m),
"Obtenha pares de fornecedores f1,f2 onde f1 fornece todos os
materiais que f2 fornece".
Faça uma verificação visual da correção da solução, analizando
o conteúdo da tabela FM.
"Obtenha pares de jogadores (j1,j2) onde j1 ganhou exatamente os mesmos torneios
que j2 ganhou"
"Obtenha pares de jogadores (j1, j2) onde j1 não ganhou nenhum dos
torneios vencidos por j2"
"Obtenha pares de jogadores (j1, j2) onde j1 ganhou todos os torneios
que j2 não ganhou ".
"Obtenha triplas de jogadores (j1, j2, j3) onde j1 ganhou todos os torneios
que os jogadores j2 e j3 ganharam"
(***) Sem usar subconsultas correlacionadas escreva e teste
um comando SQL para "obter pares de fornecedores (f1,f2)
onde f1 fornece todos os materiais que f2 fornece".
Uso de expressões-tabela em consultas complexas
O material a seguir é um resumo da seção 6.8.2 pp 163-170 do livro-texto.
SQL/92 permite o uso de expressões tabela nos seguintes casos:
Por exemplo, subconsultas gerando tabelas na cláusula from são permitidas em SQL/92.
Exemplos usando as tabelas F, M e FM:
F cross join M -- equivale ao produto cartesiano FxM
junção θ e junção exterior:
F join FM on (F.f = FM.f)
F left join FM
Exemplo:
select TX.myf, TX.mynome, TX.mymat
from (select F.f, F.nomef, FM.m
from F, FM
where F.f = FM.f) as TX(myf,mynome,mymat)
)
Exemplo:
"Obtenha para cada fornecedor o número de seus fornecimentos /
pelo número total de fornecedores".
select count(*)/(select count(*) from F)
from FM
group by f
Restrição: apenas uma lista de colunas entre parênteses pode ser usada:
esta lista é chamada de row operand.
Ela aparece na consulta clássica, já vista:
...
where (f,m) not in
(select f,m from FM)
Nota: se expressões tabelas genéricas pudessem ser usadas neste caso, consultas
complexas envolvendo uma tabela derivada contida em outra tabela derivada,
ou envolvendo a igualdade de tabelas derivadas, poderiam ser expressas
de forma bastante simples (ver livro-texto pp. 169-170) e os exemplos a seguir.
Exemplo 1 (consulta clássica):
select F.f from F
where (select m from M) =
(select distinct m from FM where f=F.f)
ou
select distinct f
from FM as F1
where (select distinct m from FM) =
(select distinct m from FM where f= F1.f)
Exemplo 2 (consulta super-clássica):
select t1.numj, t2.numj
from torneios t1, torneios t2
where t1.numj <> t2.numj
and (select distinct numt from torneios
where numj = t2.numj) in
(select distinct numt from torneios
where numj = t1.numt)
E se quisermos "obter pares de jogadores (j1,j2) onde j1 ganhou exatamente os mesmos
torneios que j2", pastaria substituir in por "=".
Compare a simplicidade desta solução com a solução da consulta (**) acima,
que segue o padrão SQL/92!.