Operadores lógicos ANY e ALL

Alguma vez você utilizou o operador lógico ANY? E o operador lógico ALL, já utilizou?
Você viu algum código SQL com esses operadores que não tenha sido em artigo técnico?

1. Introdução

Há operadores lógicos que raramente são utilizados e podemos citar os operadores ANY (ou seu equivalente SOME) e ALL. Ambos comparam um valor escalar com o resultado de uma subconsulta que retorne uma única coluna e com a utilização de qualquer um dos operadores de comparação, como

p042_quadro 1

No caso de uso na cláusula WHERE temos o seguinte esboço de código SQL

SELECT <colunas>
  from <tabela>
  where <coluna_n>  <operador_comparação>  { ANY | ALL } 
               ( <subconsulta> );

mas também se aplica a comandos condicionais, como

IF <coluna_n>  <operador_comparação>  { ANY | ALL } ( <subconsulta> )
  <comando>;

e outros mais.

Os exemplos deste artigo utilizam o banco de dados Adventure Works, disponível em Exemplos SQL.

Antes de prosseguir, uma observação. Desenvolver códigos T-SQL exige tempo e dedicação para estudos e testes, afora conhecimento técnico. Sempre que utilizo código T-SQL de outra pessoa como base eu informo o nome do autor e/ou o endereço web da página onde foi publicado, de modo a respeitar o esforço de programação de quem o desenvolveu. Do contrário tem-se o plágio que, além de ser uma violação dos direitos autorais, é uma demonstração de falta de ética profissional. Nesse ponto faço minhas as palavras de Edvaldo Castro e de Erickson Ricci presentes no artigo “PLÁGIO – Sério mesmo?”.

2. Operadores lógicos ANY e ALL

2.1. Operador ANY

2.1.1. Conceituação

Em uma comparação entre um valor escalar “x” e o resultado de uma subconsulta que retorne uma única coluna “y”, o operador ANY gera pares {x, y} e retorna TRUE caso em ao menos um dos pares o valor de “x” comparado ao valor de “y” seja verdadeiro.

Por exemplo, se “x” for 2; e se o conjunto de valores de “y” seja composto dos valores 3, 5 e 7; e ainda se a comparação for “x > y”, temos

p042_quadro 2

e o resultado final é FALSE. Ao passo que se o valor de “x” for 4, temos então

p042_quadro 3

e o resultado final é TRUE, pois em uma das comparações o resultado é verdadeiro.

2.1.2. Exemplo

Supondo que o objetivo é listar os nomes dos produtos que tiveram venda de 30 ou mais unidades em um mesmo pedido. As informações sobre cada produto estão na tabela Production.Product e quais produtos foram vendidos em cada pedido estão na tabela Sales.SalesOrderDetail.

Uma forma de obter o relatório é a seguinte:

-- código #1
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

with gSOD as (
SELECT distinct ProductID
  from Sales.SalesOrderDetail
  where OrderQty >= 30
)  
SELECT P.Name as [Nome do produto]
  from Production.Product as P
  where P.ProductID in (SELECT ProductID from gSOD);

que retorna o seguinte resultado:

p042_codigo #1

Outra solução, para quem gosta de usar JOIN em tudo, é a seguinte:

-- código #2
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

SELECT distinct P.Name as [Nome do produto]
  from Production.Product as P
       inner join Sales.SalesOrderDetail as SOD 
         on SOD.ProductID = P.ProductID
  where SOD.OrderQty >= 30;

que retorna o mesmo resultado do código SQL anterior.

Já com o operador ANY, objetivo deste item, temos a seguinte opção:

-- código #3
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

SELECT P.Name as [Nome do produto]
  from Production.Product as P
  where P.ProductID = ANY (SELECT distinct ProductID
                             from Sales.SalesOrderDetail
                             where OrderQty >= 30);

que também retorna o mesmo resultado do código #1.

E agora vêm as perguntas:

  • qual das 3 soluções é a mais rápida?
  • qual gera o plano de execução de menor custo?

Ao analisarmos os planos de execução percebemos que o otimizador de consultas (query optimizer) gerou o mesmo plano para os 3 códigos SQL:

p042_codigo #1 - plano

2.2. Operador ALL

2.2.1. Conceituação

Em uma comparação entre um valor escalar “x” e o resultado de uma subconsulta que retorne uma única coluna “y”, o operador ALL gera pares {x, y} e retorna TRUE se em todos os pares o valor de “x” comparado ao valor de “y” seja verdadeiro.

Por exemplo, se “x” for 4; e se o conjunto de valores de “y” seja composto dos valores 3, 5 e 7; e ainda se a comparação for “x > y”, temos

p042_quadro 3

e o resultado final é FALSE. Ao passo que se o valor de “x” for 8, temos então

p042_quadro 4

e o resultado final é TRUE, pois em todas as comparações o resultado é verdadeiro.

2.2.2. Exemplo

“Ticket médio” é um indicador utilizado para avaliar vendas. Neste exemplo vamos calcular o ticket médio por cliente e depois listar todos os clientes que tenham realizado compras cujo valor seja maior do que todos os valores de ticket médio por cliente.

No banco de dados Adventure Works existem tabelas que registram vendas para clientes. As informações gerais de cada venda estão na tabela Sales.SalesOrderHeader e as específicas de clientes na tabela Sales.Customer.

Utilizando o operador lógico ALL temos a seguinte solução:

-- código #4
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

with gTicketMedio as (
SELECT avg (TotalDue) as valorTicketMedio
  from Sales.SalesOrderHeader
  group by CustomerID
)
SELECT distinct O.CustomerID
  from Sales.SalesOrderHeader as O
  where O.TotalDue > ALL (SELECT valorTicketMedio 
                            from gTicketMedio);

que retorna o seguinte resultado:

p042_codigo #4

E como obter o mesmo resultado sem o uso do operador lógico ALL? Uma alternativa é encontrar o maior valor de ticket médio e então procurar na tabela de vendas quais tiveram valor acima do maior valor de ticket médio:

-- código #6
-- Autor: José Diz
-- Publicado em: Porto SQL - https://portosql.wordpress.com

with gTicketMedio as (
SELECT avg (TotalDue) as valorTicketMedio
  from Sales.SalesOrderHeader
  group by CustomerID
)  
SELECT distinct O.CustomerID
  from Sales.SalesOrderHeader as O
  where O.TotalDue > (SELECT max (valorTicketMedio) from gTicketMedio);

Qual a diferença de desempenho entre os códigos #4 e #6? Deixo aqui como exercício para você analisar os respectivos planos de execução e tirar suas conclusões.

3. Observações

A linguagem SQL tem vários recursos e um mesmo problema pode ser resolvido de várias formas. A melhor forma depende da análise do plano de execução, mas deve-se ter em mente que planos de execução para um mesmo código SQL variam de acordo com o conteúdo das tabelas envolvidas.

O objetivo deste artigo foi o de divulgar um dos recursos da linguagem SQL que raramente é utilizado.

4. Material de estudo

4.1. Documentação

 

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s