Exists-not Existss

MC536 - Consultas complexas com Exists, not Exists

Prof. Célio Guimarães IC - Unicamp

Atualizado em 09 Nov 2022

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 é:

    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.

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".
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)".

O exemplo a seguir ilustra a técnica para resolver problemas ainda mais complexos, usando consultas correlacionadas:
"(*)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:

Fixe um jogador, digamos, j.numj

Obtenha os torneios S que o jogador 817 venceu (1^a subconsulta)

Obtenha os torneios T que j.numj venceu (2^a subconsulta)

Se a tabela S - T for vazia, então j.numj é qualificado para o resultado pela cláusula where not exists

Repita para todos os jogadores (comando select externo)

     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".

Esta consulta requer a igualdade de dois conjuntos, o que pode ser feito verificando que o primeiro contém o segundo e vice versa.
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.

Exercício 2 (consulta super-clássica):
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.

Exercicio 3
"Obtenha pares de jogadores (j1,j2) onde j1 ganhou exatamente os mesmos torneios que j2 ganhou"

Exercício 4:
"Obtenha pares de jogadores (j1, j2) onde j1 não ganhou nenhum dos torneios vencidos por j2"

Exercício 5:
"Obtenha pares de jogadores (j1, j2) onde j1 ganhou todos os torneios que j2 não ganhou ".

Exercício 6
"Obtenha triplas de jogadores (j1, j2, j3) onde j1 ganhou todos os torneios que os jogadores j2 e j3 ganharam"

Exercício 7 (Consulta super-clássica):
(***) 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.

Expressões da Álgebra Relacional (AR) têm tabelas como operandos e produzem tabelas como resultado.
Tabelas na AR podem conter apenas uma coluna, ou uma linha, ou um valor escalar.
Uma expressão genérica da AR que gerasse uma tabela com o valor apropriado , poderia ser usada em SQL onde um escalar, uma linha ou uma tabela fossem desejados.
Por exemplo, subconsultas gerando tabelas na cláusula from são permitidas em SQL/92.
Tais expressões genéricas serão denominadas expressões-tabela e a(s) tabela(s) produzida(s) por uma expressão-tabela serão denominadas tabelas derivadas, possivelmente com nomes(alias). Em SQL expressões-tabela são produzidas por subconsultas.
Tabelas derivadas são temporárias: só existem durante execução do comando que as cria.
O nome de uma tabela derivada pode incluir nomes para as colunas e pode ser usado no póprio comando que a criou.
O resultado do comando select é o caso mais simples de uma tabela derivada.
SQl/92 não permite o uso de expressões tabela em muitos lugares onde seria desejável: este é o chamado problema de falta de ortogonalidade da linguagem SQL

SQL/92 permite o uso de expressões tabela nos seguintes casos:

Como operandos dos operadores de junção, união, interseção e diferença
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

Na cláusula from:
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)
           )

Como item da cláusula select
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

Como primeiro argumento de expressão booleana na cláusula where
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!.