Usando CASE para adicionar lógica a um SELECT

Ao escrever uma consulta SQL, você pode precisar obter valores de várias colunas e alterar os valores de um formulário para outro. A maneira simples de atingir este objetivo é adicionar uma expressão CASE à sua declaração SELECT. Neste artigo, vamos apresentar a sintaxe, formatos e usos da expressão CASE.

A expressão CASE é uma expressão condicional: ela avalia os dados e retorna um resultado. O resultado que ela retorna é baseado em se os dados atendem a certos critérios. Esta expressão pode ser usada nas declarações SELECT, UPDATE e DELETE ou nas cláusulas WHERE, IN, LIKE, ORDER BY, e HAVING. Vamos nos concentrar na declaração SELECT e usar os operadores IN e LIKE.

Você deve saber que existem dois tipos de expressões CASE: simples e pesquisadas. Vamos analisar ambas, mas primeiro começaremos com a sintaxe básica desta expressão.

Sintaxe do caso

A sintaxe básica da expressão CASE é apresentada abaixo:

CASE   [ column or expression ]
	WHEN  value or condition THEN when_result
	...
	ELSE else_result
END

A expressão começa com a palavra-chave CASE e termina com a palavra-chave END. Os nomes de colunas ou expressões específicas são inseridos após a palavra-chave CASE. As palavras-chave WHEN e THEN definem as condições lógicas. Após a cláusula WHEN, vemos a condição ou valor a ser comparado; o ENTÃO determina o resultado se a condição for atendida. Uma declaração opcional ELSE determina o resultado retornado se a condição WHEN não for cumprida. Se ELSE não for definida e WHEN não for atendida, um NULL será retornado.

Há dois tipos de expressões CASE: simples e pesquisadas. Cada uma tem uma sintaxe ligeiramente diferente, e cada uma funciona um pouco diferente.

Vamos começar com o formato simples.

CASO: O Formato Simples

O simples CASE compara um valor a uma ou mais condições WHEN. Se ela atende a uma condição WHEN, o resultado THEN é devolvido. Aqui está como a sintaxe é:

CASE column_or_expression
    WHEN  value THEN when_result
    ...
    ELSE else_result
END

Em uma simples expressão CASE, o nome da coluna ou expressão a ser avaliada é absolutamente necessário. Ela vai após a palavra-chave CASE. O valor definido vai após o WHEN. Se o valor avaliado for o mesmo que o valor definido, o resultado definido em ENTÃO é retornado. Se o valor da coluna ou expressão não corresponder a nenhum valor WHEN, então o resultado de ELSE é exibido. Lembre-se, ELSE é opcional.

As seguintes consultas de exemplo são baseadas em dados do animal tabela, como mostrado abaixo:

Olhe para este simples CASE:

CASE  zoo 
  WHEN 1 THEN 'Brookfield Zoo'
  WHEN 2 THEN 'Dublin zoo'
  ELSE 'unknown'
END 

Este exemplo seleciona os dados da tabela animal tabela e mostra o nome do zoológico atual do animal. Se o zoo=1, então Brookfield Zoo será exibido; se o zoo=2 então veremos Dublin Zoo; todos os outros casos serão exibidos como unknown.

O resultado é:

Observe que se o valor da coluna do zoológico não for igual a nenhum dos valores das cláusulas WHEN (ou é NULL), o resultado é unknown.

Até agora, consideramos simples expressões CASE que têm um nome de coluna após a palavra-chave CASE. Você também pode colocar uma expressão após a palavra-chave CASE.

Imagine que, além do peso dos animais em quilogramas, você também gostaria de exibir o peso total dos mamíferos por espécie - mas somente se estes valores forem especiais. Neste caso, três números característicos devem ser determinados. Eles serão exibidos como cordas. Se o peso total não for um destes três números, então outros serão mostrados.

Aqui está a consulta:

SELECT name, weight, number_of_animal,
		( CASE  number_of_animal*weight 
  			WHEN 18 THEN 'small'
  			WHEN 360 THEN 'middle'
  			WHEN 24000 THEN 'big'
  			ELSE 'other'
		  END 
                 ) as total_weight
FROM  animal ;

Veja o resultado:

Esta expressão CASE tem uma cláusula ELSE que exibe outra. Sem o ELSE, todos os pesos não correspondentes exibiriam um NULL. Aqui está o mesmo código sem um ELSE:

SELECT  name, weight, number_of_animal,
			 CASE  number_of_animal*weight 
  				WHEN 18 THEN 'small'
  				WHEN 360 THEN 'middle'
  				WHEN 24000 THEN 'big'
			 END 
                         as total_weight
FROM  animal ;

E os resultados:

Usando Aliases em Declarações de Caso

A expressão CASE pode ter um pseudônimo, ou um nome para a nova coluna resultante. A consulta acima, agora com um pseudônimo, se parece com isto:

SELECT name, zoo, 
			  CASE  zoo 
  			     WHEN 1 THEN 'Brookfield Zoo'
  			     WHEN 2 THEN 'Dublin zoo'
  			     ELSE 'unknown'
			   END 
			  as name_zoo
FROM  animal ;

E o resultado:

Usando CASE com outros tipos de dados

Você pode usar a expressão CASE com outros tipos de dados, tais como CHARACTER.

Confira esta consulta:

SELECT  name, zoo, 
			   ( CASE  country 
 			 	WHEN 'Russia' THEN 'Europe'
  				WHEN 'Congo' THEN 'Africa'
 				WHEN 'Turkey' THEN 'Asia'
  				WHEN 'Australia' THEN 'Australia' 
  				WHEN 'China' THEN 'Asia'
  				ELSE 'unknown'
				END 
                            ) as continent
FROM  animal ;

Esta consulta seleciona os dados da tabela e mostra o continente de onde o animal vem. O continente é baseado na coluna country, que usa o tipo de dados CHARACTER.

Este é o resultado:

A Expressão do CASO PESQUISADO

A expressão pesquisada CASE é o formato mais comumente utilizado. Mas ao invés de comparar uma coluna ou expressão contra um conjunto definido de valores, uma expressão pesquisada pode comparar múltiplas condições WHEN e determinar um resultado. Isto faz com que uma CASE pesquisada seja a melhor escolha para uma lógica mais complicada.

Aqui está a sintaxe:

CASE 
    WHEN  conditions THEN when_result
    ...
    ELSE else_result
END

Mais uma vez, a expressão começa com a palavra-chave CASE, mas não há nenhum nome de coluna ou expressão seguinte. Pode haver múltiplas construções QUANDO - THEN, assim como a simples CASE. Mas após a palavra-chave WHEN, inserimos uma ou mais condições ao invés de estabelecer valores. Estas condições compararão um valor recuperado e retornarão o primeiro que se encontrar como TRUE. Isto fará com que o resultado de THEN seja exibido. Se todas as condições retornarem FALSE, então o resultado de ELSE será exibido. E novamente, ELSE é opcional.

Isto pode parecer difícil de entender, por isso, vamos investigar nossa consulta de exemplo:

SELECT  name, weight,
		  CASE 
  		    WHEN weight>1000 THEN 'big'
  		    WHEN weight BETWEEN 100  AND 1000 THEN 'middle'
		    WHEN weight<100 THEN 'small'
	            ELSE '---'
		  END 
		  as description_weight
FROM  animal ;

Veja os resultados:

A primeira cláusula de quando verificar se weight é maior que 1.000 quilos. Se for verdade, a cadeia big é devolvida. A próxima cláusula WHEN verifica se weight está entre 100 e 1.000 quilos. Se for, a corda middle será devolvida. Neste caso, o operador BETWEEN AND é usado para definir a faixa. Para animais cujo weight seja inferior a 100 quilogramas, a cadeia de caracteres small será exibida. Em qualquer outro caso, o valor de retorno será "- ".

Usando CASO com LIKE

Você pode usar outros operadores com a expressão CASE. No exemplo a seguir, utilizaremos o operador LIKE, que combina padrões em substratos de busca. (Para mais informações sobre o operador LIKE, leia Utilizando LIKE para combinar padrões em SQL).

Aqui está o exemplo:

SELECT name, 
		( CASE 
  			WHEN name LIKE '____' THEN '4 letters'
  			WHEN name LIKE '_____' THEN '5 letters'
 			ELSE 'other'
		  END 
		) as description
FROM  animal ;

E aqui está o resultado:

Nesta expressão pesquisada CASE, o operador LIKE testa se o nome do animal consiste de quatro ou cinco letras. Em outros casos - como no caso do elefante africano - o valor de retorno é outro.

Usando CASO com IN

No próximo exemplo, vamos experimentar o operador IN. Lembra-se da simples expressão CASE que devolveu um nome de continente baseado no país associado a um animal? O código para esta consulta era longo, graças a muitas construções separadas WHEN - THEN. Abaixo, damos outra olhada no código antigo:

SELECT  name, country,
			( CASE  country 
  				WHEN 'Russia' THEN 'Europe'
  				WHEN 'Congo' THEN 'Africa'
  				WHEN 'Turkey' THEN 'Asia'
 				WHEN 'Australia' THEN 'Australia' 
  				WHEN 'China' THEN 'Asia'
  				ELSE 'unknown'
			  END 
                         ) as continent
FROM  animal ;

O operador IN permite que você reduza a quantidade de código. Veja uma pesquisa em CASE com o operador IN:

SELECT name, country, 
		( CASE   
 		    WHEN country IN ('Russia') THEN 'Europe'
 		    WHEN country IN ('Congo') THEN 'Africa'
		    WHEN country IN ('Turkey','China') THEN 'Asia'
 		    WHEN country IN ('Australia') THEN 'Australia' 
	            ELSE 'unknown'
		  END 
		) as continent
FROM  animal ;

Podemos usar o operador IN para listar vários países no mesmo continente. Se o valor da coluna country corresponder a uma das cadeias do IN, o resultado é o continente da cláusula THEN. É claro, se o nome do país não estiver listado em nenhum operador IN, ELSE faz o pontapé inicial e retorna um valor unknown.

Veja a tabela de resultados abaixo:

Condições de pedido no CASE

Portanto, agora você pode criar condições na cláusula WHEN. Mas é importante construí-las com precisão. Se seu valor de teste corresponder à primeira cláusula quando a cláusula THEN for devolvida e a expressão CASE estiver terminada. Mesmo que um valor realmente atenda a várias condições WHEN, a primeira condição que ele atenda é a única que será devolvida.

Veja o exemplo a seguir:

SELECT name, weight,
				( CASE 
  					WHEN weight<1000 THEN 'not big'
  					WHEN weight<100 THEN 'not middle'
  					WHEN weight<10 THEN 'very small'
 					ELSE '---'
			  	  END 
				) as description_weight
FROM  animal ;

Neste caso, o valor weight será o primeiro a se encaixar na condição inicial WHEN. Em nossa tabela, a maioria dos animais terá um valor weight de menos de 1.000 kg. Somente os animais com um weight associado de 1.000 ou mais (ou que tenham um valor NULL em weight) terão um valor "-" devolvido.

Aqui estão os resultados:

Lembre-se de que a ordem do QUANDO as condições são muito importantes; elas podem afetar os resultados que você obtém de volta.

Aprendendo mais sobre a Expressão do Caso SQL

Ser capaz de usar uma expressão devidamente formatada CASE é uma boa capacidade de cultivar. Ela lhe permite obter os resultados que você espera de suas consultas.

Se você estiver interessado em saber mais sobre a expressão CASE, confira LearnSQL's Funções Comuns em SQL curso. Ele lhe mostrará como construir consultas que usam esta expressão e muitas outras. Você também terá a oportunidade de praticar suas habilidades. Experimente hoje mesmo!