9.16. Expressões de subconsulta

9.16.1. EXISTS

EXISTS ( subconsulta )

O argumento do EXISTS é uma declaração SELECT arbitrária, ou uma subconsulta. A subconsulta é processada para determinar se retorna alguma linha. Se retornar pelo menos uma linha, o resultado de EXISTS é "verdade"; se a subconsulta não retornar nenhuma linha, o resultado de EXISTS é "falso".

A subconsulta pode referenciar variáveis da consulta que a envolve, que atuam como constantes durante a execução da subconsulta.

A subconsulta geralmente só é processada até ser determinado se retorna pelo menos uma linha, e não até o fim. Não é razoável escrever uma subconsulta que tenha efeitos colaterais (tal como chamar uma função de seqüência); pode ser difícil prever se o efeito colateral ocorrerá ou não.

Como o resultado depende apenas de alguma linha ser retornada, e não do conteúdo da linha, normalmente não há interesse na saída da subconsulta. Uma convenção de codificação habitual é escrever todos os testes de EXISTS na forma EXISTS(SELECT 1 WHERE …). Entretanto, existem exceções para esta regra, como as subconsultas que utilizam INTERSECT.

Este exemplo simples é como uma junção interna em col2, mas produz no máximo uma linha de saída para cada linha de tab1, mesmo havendo várias linhas correspondentes em tab2:

SELECT col1 FROM tab1
    WHERE EXISTS(SELECT 1 FROM tab2 WHERE col2 = tab1.col2);

CREATE TEMPORARY TABLE frutas (id SERIAL PRIMARY KEY, nome TEXT);
INSERT INTO frutas VALUES (DEFAULT, 'banana');
INSERT INTO frutas VALUES (DEFAULT, 'maçã');
CREATE TEMPORARY TABLE alimentos (id SERIAL PRIMARY KEY, nome TEXT);
INSERT INTO alimentos VALUES (DEFAULT, 'maçã');
INSERT INTO alimentos VALUES (DEFAULT, 'espinafre');
SELECT nome, CASE WHEN EXISTS (SELECT nome FROM frutas WHERE nome=a.nome)
                  THEN 'sim'
                  ELSE 'não'
             END AS fruta
FROM alimentos a;

  nome    | fruta
-----------+-------
 maçã      | sim
 espinafre | não
(2 linhas)

9.16.2. IN

expressão IN (subconsulta)

O lado direito é uma subconsulta entre parênteses, que deve retornar exatamente uma coluna. A expressão à esquerda é processada e comparada com cada linha do resultado da subconsulta. O resultado do IN é "verdade" se for encontrada uma linha igual na subconsulta. O resultado é "falso" se não for encontrada nenhuma linha igual (incluindo o caso especial onde a subconsulta não retorna nenhuma linha).

Deve ser observado que, se o resultado da expressão à esquerda for nulo, ou se não houver nenhum valor igual à direita e uma das linhas à direita tiver o valor nulo, o resultado da construção IN será nulo, e não falso. Isto está de acordo com as regras normais do SQL para combinações booleanas de valores nulos.

Da mesma forma que no EXISTS, não é razoável assumir que a subconsulta será processada até o fim.

(expressão [, expressão …]) IN (subconsulta)

O lado direito desta forma do IN é uma subconsulta entre parênteses, que deve retornar exatamente tantas colunas quantas forem as expressões na lista do lado esquerdo. As expressões do lado esquerdo são processadas e comparadas, por toda a largura, com cada linha do resultado da subconsulta. O resultado do IN é "verdade" se for encontrada alguma linha igual na subconsulta. O resultado é "falso" se nenhuma linha igual for encontrada (incluindo o caso especial onde a subconsulta não retorna nenhuma linha).

Da forma habitual, os valores nulos nas linhas são combinados pelas regras normais das expressões booleanas do SQL. Duas linhas são consideradas iguais se todos os seus membros correspondentes forem não nulos e iguais; as linhas não são iguais se qualquer membro correspondente for não nulo e não for igual; senão o resultado da comparação da linha é desconhecido (nulo). Se o resultado para todas as linhas for diferente ou nulo, com pelo menos um nulo, então o resultado do IN será nulo.

SELECT nome, CASE WHEN nome IN (SELECT nome FROM frutas)
                  THEN 'sim'
                  ELSE 'não'
             END AS fruta
FROM alimentos;

  nome    | fruta
-----------+-------
 maçã      | sim
 espinafre | não
(2 linhas)

9.16.3. NOT IN

expressão NOT IN (subconsulta)

O lado direito é uma subconsulta entre parênteses, que deve retornar exatamente uma coluna. A expressão à esquerda é processada e comparada com cada linha do resultado da subconsulta. O resultado de NOT IN é "verdade" se somente forem encontradas linhas diferentes na subconsulta (incluindo o caso especial onde a subconsulta não retorna nenhuma linha). O resultado é "falso" se for encontrada alguma linha igual.

Deve ser observado que, se o resultado da expressão à esquerda for nulo, ou se não houver nenhum valor igual à direita e uma das linhas à direita tiver o valor nulo, o resultado da construção NOT IN será nulo, e não verdade. Isto está de acordo com as regras normais do SQL para combinações booleanas de valores nulos.

Da mesma forma que no EXISTS, não é razoável assumir que a subconsulta será processada até o fim.

(expressão [, expressão …]) NOT IN (subconsulta)

O lado direito desta forma do NOT IN é uma subconsulta entre parênteses, que deve retornar exatamente tantas colunas quantas forem as expressões na lista do lado esquerdo. As expressões do lado esquerdo são processadas e comparadas, por toda a largura, com cada linha do resultado da subconsulta. O resultado do NOT IN é "verdade" se forem encontradas somente linhas diferentes na subconsulta (incluindo o caso especial onde a subconsulta não retorna nenhuma linha). O resultado é "falso" se alguma linha igual for encontrada.

Da forma habitual, os valores nulos nas linhas são combinados pelas regras normais das expressões booleanas do SQL. Duas linhas são consideradas iguais se todos os seus membros correspondentes forem não nulos e iguais; as linhas não são iguais se qualquer membro correspondente for não nulo e não for igual; senão o resultado da comparação da linha é desconhecido (nulo). Se o resultado para todas as linhas for diferente ou nulo, com pelo menos um nulo, então o resultado do NOT IN será nulo.

9.16.4. ANY/SOME

expressão operador ANY (subconsulta)
expressão operador SOME (subconsulta)

O lado direito é uma subconsulta entre parênteses, que deve retornar exatamente uma coluna. A expressão à esquerda é processada e comparada com cada linha do resultado da subconsulta usando o operador especificado, devendo produzir um resultado booleano. O resultado do ANY é "verdade" se for obtido algum resultado verdade. O resultado é "falso" se nenhum resultado verdade for encontrado (incluindo o caso especial onde a subconsulta não retorna nenhuma linha). [3]

SOME é sinônimo de ANY. IN equivale a = ANY.

Deve ser observado que, se não houver nenhuma comparação bem sucedida, e pelo menos uma linha da direita gerar nulo como resultado do operador, o resultado da construção ANY será nulo, e não falso. Isto está de acordo com as regras normais do SQL para combinações booleanas de valores nulos.

Do mesmo modo que no EXISTS, não é razoável supor que a subconsulta será processada até o fim.

(expressão [, expressão …]) operador ANY (subconsulta)
(expressão [, expressão …]) operador SOME (subconsulta)

O lado direito desta forma do ANY é uma subconsulta entre parênteses, que deve retornar exatamente tantas colunas quantas forem as expressões existentes na lista do lado esquerdo. As expressões do lado esquerdo são processadas e comparadas, por toda a largura, com cada linha do resultado da subconsulta utilizando o operador especificado. Atualmente, somente são permitidos os operadores = e <> em consultas ANY para toda a largura da linha. O resultado do ANY é "verdade" se for encontrada alguma linha igual ou diferente, respectivamente. O resultado será "falso" se não for encontrada nenhuma linha deste tipo (incluindo o caso especial onde a subconsulta não retorna nenhuma linha).

Como usual, os valores nulos nas expressões ou nas linhas da subconsulta são combinados conforme as regras normais do SQL para expressões booleanas. Duas linhas são consideradas iguais se todos os membros correspondentes forem não nulos e iguais; as linhas não são iguais se algum membro correspondente for não nulo e for diferente; caso contrário, o resultado da comparação da linha é desconhecido (nulo). Havendo pelo menos um resultado de linha nulo, então o resultado de ANY não pode ser falso; será verdade ou nulo.

SELECT nome, CASE WHEN nome = ANY (SELECT nome FROM frutas)
                  THEN 'sim'
                  ELSE 'não'
             END AS fruta
FROM alimentos;

  nome    | fruta
-----------+-------
 maçã      | sim
 espinafre | não
(2 linhas)

9.16.5. ALL

expressão operador ALL (subconsulta)

O lado direito é uma subconsulta entre parênteses, que deve retornar exatamente uma coluna. A expressão à esquerda é processada e comparada com cada linha do resultado da subconsulta usando o operador especificado, devendo produzir um resultado booleano. O resultado do ALL é "verdade" se o resultado de todas as linhas for verdade (incluindo o caso especial onde a subconsulta não retorna nenhuma linha). O resultado é "falso" se for encontrado algum resultado falso.

NOT IN equivale a <> ALL.

Deve ser observado que, se todas as comparações forem bem-sucedidas, mas pelo menos uma linha da direita gerar nulo como resultado do operador, o resultado da construção ALL será nulo, e não verdade. Isto está de acordo com as regras normais do SQL para combinações booleanas de valores nulos.

Do mesmo modo que no EXISTS, não é razoável supor que a subconsulta será processada até o fim.

(expressão [, expressão …]) operador ALL (subconsulta)

O lado direito desta forma do ALL é uma subconsulta entre parênteses, que deve retornar exatamente tantas colunas quantas forem as expressões existentes na lista do lado esquerdo. As expressões do lado esquerdo são processadas e comparadas, por toda a largura, com cada linha do resultado da subconsulta utilizando o operador especificado. Atualmente, somente os operadores = e <> são permitidos em consultas ALL para toda a largura da linha. O resultado do ALL é "verdade" se todas as linhas da subconsulta forem iguais ou diferentes, respectivamente (incluindo o caso especial onde a subconsulta não retorna nenhuma linha). O resultado é "falso" se for encontrada alguma linha que seja diferente ou igual, respectivamente.

Como usual, os valores nulos nas linhas são combinados conforme as regras normais do SQL para expressões booleanas. Duas linhas são consideradas iguais se todos os membros correspondentes forem não nulos e iguais; as linhas não são iguais se algum membro correspondente for não nulo e for diferente; caso contrário, o resultado da comparação da linha é desconhecido (nulo). Havendo pelo menos um resultado de linha nulo, então o resultado de ALL não pode ser verdade; será falso ou nulo.

9.16.6. Comparação de toda a linha

(expressão [, expressão …]) operador (subconsulta)

O lado esquerdo é uma lista de expressões escalares. O lado direito é uma subconsulta entre parênteses, que deve retornar exatamente tantas colunas quantas forem as expressões do lado esquerdo. Além disso, a subconsulta não pode retornar mais de uma linha (Se retornar zero linhas, o resultado é considerado como sendo nulo). O lado esquerdo é processado e comparado, por toda a largura, com a única linha de resultado da subconsulta. Atualmente, somente os operadores = e <> são permitidos na comparação por toda a largura da linha. O resultado será "verdade" se as duas linhas forem iguais ou diferentes, respectivamente.

Como usual, os valores nulos são combinados conforme as regras normais do SQL para expressões booleanas. Duas linhas são consideradas iguais se todos os membros correspondentes forem iguais e não nulos; as linhas não são iguais se algum membro correspondente for não nulo e for diferente; caso contrário, o resultado da comparação da linha é desconhecido (nulo).