Uso de CASE para añadir lógica a un SELECT

Al escribir una consulta SQL, es posible que necesite obtener valores de varias columnas y cambiar los valores de una forma a otra. La forma más sencilla de lograr este objetivo es añadir una expresión CASE a su sentencia SELECT. En este artículo, le presentaremos la sintaxis, los formatos y los usos de la expresión CASE.

La expresión CASE es una expresión condicional: evalúa datos y devuelve un resultado. El resultado que devuelve se basa en si los datos cumplen ciertos criterios. Esta expresión puede utilizarse en las sentencias SELECT, UPDATE, y DELETE o en las cláusulas WHERE, IN, LIKE, ORDER BY, y HAVING. Nos centraremos en la sentencia SELECT y utilizaremos los operadores IN y LIKE.

Debes saber que hay dos tipos de expresiones CASE: simples y buscadas. Veremos ambas, pero primero comenzaremos con la sintaxis básica de esta expresión.

Sintaxis de CASE

La sintaxis básica de la expresión CASE se presenta a continuación:

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

La expresión comienza con la palabra clave CASE y termina con la palabra clave END. Los nombres de las columnas o expresiones específicas se introducen después de la palabra clave CASE. Las palabras clave WHEN y THEN definen las condiciones lógicas. Después de la cláusula WHEN, vemos la condición o valor a comparar; el THEN determina el resultado si se cumple la condición. Una sentencia opcional ELSE determina el resultado devuelto si la condición WHEN no se cumple. Si no se define ELSE y no se cumple WHEN, se devolverá NULL.

Existen dos tipos de expresiones CASE: simples y buscadas. Cada una tiene una sintaxis ligeramente diferente, y cada una funciona de forma un poco distinta.

Comenzaremos con el formato simple.

CASO: El formato simple

El formato simple CASE compara un valor con una o más condiciones WHEN. Si cumple una condición WHEN, se devuelve el resultado THEN. La sintaxis es la siguiente:

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

En una expresión simple de CASE, el nombre de la columna o expresión a evaluar es absolutamente necesario. Va después de la palabra clave CASE. El valor establecido va después de WHEN. Si el valor evaluado es el mismo que el valor establecido, se devuelve el resultado definido en THEN. Si el valor de la columna o expresión no coincide con ningún valor de WHEN, entonces se muestra el resultado de ELSE. Recuerde que ELSE es opcional.

Las siguientes consultas de ejemplo se basan en los datos de la animal como se muestra a continuación:

Observe este sencillo CASE:

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

Este ejemplo selecciona los datos de la tabla animal y muestra el nombre del zoológico actual del animal. Si zoo=1, entonces se mostrará Brookfield Zoo; si zoo=2 entonces veremos Dublin Zoo; todos los demás casos se mostrarán como unknown.

El resultado es:

Observe que si el valor de la columna zoo no es el mismo que ninguno de los valores de las cláusulas de WHEN (o es NULL), el resultado es unknown.

Hasta ahora, hemos considerado expresiones simples de CASE que tienen un nombre de columna después de la palabra clave CASE. También se puede poner una expresión después de la palabra clave CASE.

Imagínese que, además del peso de los animales en kilogramos, también desea mostrar el peso total de los mamíferos por especie, pero sólo si estos valores son especiales. En este caso, hay que determinar tres números característicos. Se mostrarán como cadenas. Si el peso total no es uno de estos tres números, entonces se mostrarán otros.

Esta es la 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 ;

Mire el resultado:

Esta expresión CASE tiene una cláusula ELSE que muestra other. Sin la cláusula ELSE, todos los pesos no coincidentes mostrarían un NULL. Aquí está el mismo código sin 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 ;

Y los resultados:

Uso de alias en las sentencias CASE

La expresión CASE puede tener un alias, o un nombre para la nueva columna resultante. La consulta anterior, ahora con un alias, tiene el siguiente aspecto:

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

Y el resultado:

Uso de CASE con otros tipos de datos

Puede utilizar la expresión CASE con otros tipos de datos, como CHARACTER.

Compruebe 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 selecciona los datos de la tabla y muestra el continente del que procede el animal. El continente se basa en la columna country, que utiliza el tipo de datos CHARACTER.

Este es el resultado:

La expresión CASE buscada

La expresión buscada CASE es el formato más utilizado. Pero en lugar de comparar una columna o expresión con un conjunto definido de valores, una expresión buscada puede comparar múltiples condiciones de WHEN y determinar un resultado. Esto hace que una expresión buscada CASE sea la mejor opción para una lógica más complicada.

Esta es la sintaxis:

CASE 
    WHEN  conditions THEN when_result
    ...
    ELSE else_result
END

Una vez más, la expresión comienza con la palabra clave CASE, pero no hay ningún nombre de columna o expresión a continuación. Puede haber múltiples construcciones WHEN - THEN, al igual que el CASE simple. Pero después de la palabra clave WHEN, introducimos una o más condiciones en lugar de establecer valores. Estas condiciones compararán un valor recuperado y devolverán el primero que encuentre como TRUE. Esto hará que se muestre el resultado de THEN. Si todas las condiciones devuelven FALSE, entonces se mostrará el resultado de ELSE. Y de nuevo, ELSE es opcional.

Esto puede parecer difícil de entender, así que vamos a profundizar en nuestra consulta de ejemplo:

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 ;

Mira los resultados:

La primera cláusula WHEN comprueba si weight es mayor de 1.000 kilogramos. Si es verdadera, se devuelve la cadena big. La siguiente condición WHEN comprueba si weight está entre 100 y 1.000 kilogramos. Si lo hace, se devuelve la cadena middle. En este caso, se utiliza el operador BETWEEN AND para definir el rango. Para los animales cuyo weight es inferior a 100 kilogramos, se mostrará la cadena small. En los demás casos, el valor de retorno será "- ".

Uso de CASE con LIKE

Puede utilizar otros operadores con la expresión CASE. En el siguiente ejemplo, utilizaremos el operador LIKE, que coincide con patrones en las subcadenas de búsqueda. (Para obtener más información sobre el operador LIKE, lea Uso de LIKE para coincidir con patrones en SQL).

Este es el ejemplo:

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

Y aquí está el resultado:

En esta expresión buscada CASE, el operador LIKE comprueba si el nombre del animal está formado por cuatro o cinco letras. En otros casos - como en el elefante africano - el valor de retorno es otro.

Uso de CASE con IN

En el siguiente ejemplo, probaremos el operador IN. ¿Recuerda la sencilla expresión CASE que devolvía el nombre de un continente en función del país asociado a un animal? El código de esta consulta era largo, gracias a un montón de construcciones separadas WHEN - THEN. A continuación, le ofrecemos otro vistazo al antiguo código:

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 ;

El operador IN permite reducir la cantidad de código. Mira una búsqueda CASE con el 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 utilizar el operador IN para listar varios países del mismo continente. Si el valor de la columna country coincide con una de las cadenas de IN, el resultado es el continente de la cláusula THEN. Por supuesto, si el nombre del país no aparece en ningún operador de IN, ELSE entra en acción y devuelve un valor de unknown.

Observe la siguiente tabla de resultados:

Ordenar las condiciones en CASE

Así que ahora puede construir condiciones en la cláusula WHEN. Pero es importante construirlas con precisión. Si su valor de prueba coincide con la primera cláusula WHEN, se devuelve la cláusula THEN y la expresión CASE termina. Incluso si un valor cumple varias condiciones de WHEN, la primera condición que cumple es la única que se devolverá.

Observe el siguiente ejemplo:

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 ;

En este caso, el valor weight será el primero que cumpla la condición inicial WHEN. En nuestra tabla, la mayoría de los animales tendrán un valor de weight inferior a 1.000 kg. Sólo los animales con un weight asociado de 1.000 o más (o que tengan un valor NULL en weight) obtendrán un valor "-".

Estos son los resultados:

Recuerde que el orden de las condiciones WHEN es muy importante; pueden afectar a los resultados que obtenga.

Aprendiendo más sobre la Expresión CASE de SQL

Ser capaz de usar una expresión CASE con el formato adecuado es una buena habilidad a cultivar. Le permite obtener los resultados que espera de sus consultas.

Si está interesado en aprender más sobre la expresión CASE, consulte el curso de LearnSQL Funciones estándar de SQL de LearnSQL. Te mostrará cómo construir consultas que utilicen esta expresión y muchas otras. También tendrás la oportunidad de practicar tus habilidades. ¡Pruébalo hoy mismo!