CASE y los valores que faltan

En resumen: CASE WHEN devuelve un valor distinto según una condición, COALESCE sustituye NULL por un valor por defecto, y NULLIF convierte un valor dado en NULL.

Problema

Palabras en lugar de códigos

En la página «Mis pedidos», el cliente ve el estado tal como está en la base: paid, shipped. Los códigos son cómodos para el programa, pero el cliente espera «Pagado» y «En camino».

El sitio web quiere además un filtro «barato, medio, caro». En la base no existe esa columna, solo un precio.

La idea

CASE WHEN

CASE da un valor distinto según una condición:

SQLbase de datos: tienda

SELECT name, price,
       CASE
         WHEN price < 2 THEN 'barato'
         WHEN price < 10 THEN 'medio'
         ELSE 'caro'
       END AS price_group
FROM products
WHERE category_id IN (4, 8);

SELECT name, price, CASE WHEN price < 2 THEN 'barato' WHEN price < 10 THEN 'medio' ELSE 'caro' END AS price_group FROM products WHERE category_id IN (4, 8);

▶ EjecutarRestaurar la consultaBase nueva

Las condiciones se comprueban de arriba abajo. El primer WHEN verdadero da el valor del THEN que tiene al lado, y a los demás no se los mira. ELSE es para todo aquello para lo que nada es verdadero. END cierra la expresión, y AS le da un nombre.

Cuando comparas una columna con varios valores, hay una forma más corta: la columna se escribe una vez después de CASE, y cada WHEN lleva solo el valor:

SQLbase de datos: tienda

SELECT id, status,
       CASE status
         WHEN 'new' THEN 'Recibido'
         WHEN 'paid' THEN 'Pagado'
       END AS status_text
FROM orders
WHERE id BETWEEN 57 AND 59;

SELECT id, status, CASE status WHEN 'new' THEN 'Recibido' WHEN 'paid' THEN 'Pagado' END AS status_text FROM orders WHERE id BETWEEN 57 AND 59;

▶ EjecutarRestaurar la consultaBase nueva

Pruébalo

Los estados en palabras

Muestra el número, la fecha y el estado en palabras de cada pedido, en una columna llamada status_text: new es «Recibido», paid es «Pagado», shipped es «En camino», delivered es «Entregado», cancelled es «Cancelado».

SQLbase de datos: tienda

SELECT id, ordered_at, status FROM orders;

SELECT id, ordered_at, status FROM orders;

Comprobar▶ Ejecutar

Míralo

Los caros desaparecieron

Un compañero reescribe los grupos de precio con >. Ejecútalo y mira la cocina:

SQLbase de datos: tienda

SELECT name, price,
       CASE
         WHEN price > 2 THEN 'medio'
         WHEN price > 10 THEN 'caro'
         ELSE 'barato'
       END AS price_group
FROM products
WHERE category_id IN (4, 8);

SELECT name, price, CASE WHEN price > 2 THEN 'medio' WHEN price > 10 THEN 'caro' ELSE 'barato' END AS price_group FROM products WHERE category_id IN (4, 8);

▶ EjecutarRestaurar la consultaBase nueva

1. ¿Por qué la sartén de 24,90 € salió como «medio»?

Porque gana el primer WHEN verdadero: 24.90 > 2 es verdadero, y «> 10» nunca se alcanzaPorque ELSE lo recoge todoPorque CASE no funciona con decimales

Comprobar

Una cosa más: si no hay ELSE y ningún WHEN es verdadero, el resultado es NULL. Los panes reciben una nota solo cuando están agotados o quedan pocos:

SQLbase de datos: tienda

SELECT name, stock,
       CASE
         WHEN stock = 0 THEN 'agotado'
         WHEN stock < 30 THEN 'últimas unidades'
       END AS note
FROM products
WHERE category_id = 3;

SELECT name, stock, CASE WHEN stock = 0 THEN 'agotado' WHEN stock < 30 THEN 'últimas unidades' END AS note FROM products WHERE category_id = 3;

▶ EjecutarRestaurar la consultaBase nueva

A veces es justo lo que quieres. Si no, escribe ELSE.

La idea

COALESCE y NULLIF

COALESCE(a, b, …) devuelve el primer valor que no es NULL. Así pones una palabra en lugar de lo que falta:

SQLbase de datos: tienda

SELECT name, COALESCE(city, 'desconocida') AS city
FROM customers
WHERE id BETWEEN 10 AND 13;

SELECT name, COALESCE(city, 'desconocida') AS city FROM customers WHERE id BETWEEN 10 AND 13;

▶ EjecutarRestaurar la consultaBase nueva

A veces «falta» se guarda no como NULL sino como texto vacío. En los dos ejemplos siguientes la base tiene un cliente más, Anna, guardada a través de un formulario que envía '' en lugar de NULL. COALESCE no la recoge, porque el texto vacío es un valor:

SQLbase de datos: tienda

SELECT name, COALESCE(city, 'desconocida') AS shown, city IS NULL AS no_city
FROM customers
WHERE id >= 12 AND (city IS NULL OR city = '');

SELECT name, COALESCE(city, 'desconocida') AS shown, city IS NULL AS no_city FROM customers WHERE id >= 12 AND (city IS NULL OR city = '');

▶ EjecutarRestaurar la consultaBase nueva

NULLIF(a, b) da NULL cuando a es igual a b, y a en caso contrario. Así el texto vacío se vuelve NULL, y COALESCE recoge a los dos clientes:

SQLbase de datos: tienda

SELECT name, COALESCE(NULLIF(city, ''), 'desconocida') AS shown
FROM customers
WHERE id >= 12 AND (city IS NULL OR city = '');

SELECT name, COALESCE(NULLIF(city, ''), 'desconocida') AS shown FROM customers WHERE id >= 12 AND (city IS NULL OR city = '');

▶ EjecutarRestaurar la consultaBase nueva

Más precisamente, para curiosos

NULLIF se usa también en la división: total / NULLIF(count, 0). Cuando count es 0, el divisor se vuelve NULL y el resultado también. En SQLite dividir entre cero da NULL de todas formas, pero en PostgreSQL es un error que detiene toda la consulta.

Pruébalo

La línea para el mensajero

Corrige la línea «nombre, ciudad» de la lección sobre texto: cuando falte la ciudad, en su lugar debe decir «sin ciudad». La columna debe llamarse contact.

SQLbase de datos: tienda

SELECT name || ', ' || city AS contact FROM customers;

SELECT name || ', ' || city AS contact FROM customers;

Comprobar▶ Ejecutar

Ejercicio

Dos tareas

1. Junto a cada producto, el sitio muestra las existencias en palabras: «agotado» con 0, «últimas unidades» por debajo de 10, y si no «en existencia». Muestra el nombre, las existencias y las palabras en una columna llamada availability.

Las existencias en palabras de cada producto:

SQLbase de datos: tienda

Comprobar▶ Ejecutar

2. El almacén trabaja en este orden: primero prepara los pedidos pagados (paid), luego mira los nuevos (new), y al final revisa los enviados (shipped). Muestra el número, el estado y la fecha de esos pedidos en el orden de trabajo, y dentro de un mismo estado el más antiguo primero. Con la misma fecha, primero el número menor.

Los pedidos abiertos en el orden en que los atiende el almacén:

SQLbase de datos: tienda

Comprobar▶ Ejecutar

Más precisamente, para curiosos

SQLite tiene también IIF(condición, si es verdadera, si no): un CASE corto con un WHEN y un ELSE. SQL Server lo tiene también, pero PostgreSQL no. CASE es la forma estándar y funciona en todas partes.

SQLbase de datos: tienda

SELECT name, stock, IIF(stock = 0, 'agotado', 'disponible') AS shown
FROM products
WHERE category_id = 3;

SELECT name, stock, IIF(stock = 0, 'agotado', 'disponible') AS shown FROM products WHERE category_id = 3;

▶ EjecutarRestaurar la consultaBase nueva

Test

Ponte a prueba

1. ¿Qué da CASE cuando ningún WHEN es verdadero y no hay ELSE?

NULLUn errorTexto vacío

2. ¿Qué devuelve COALESCE(NULL, NULL, 'Sofía', 'Varna')?

SofíaNULLVarna

3. ¿Qué devuelve NULLIF(city, '') para una ciudad que es texto vacío?

Un error''NULL

4. ¿Dónde puede ir CASE?

Solo en SELECTEn cualquier sitio donde pueda ir una expresión: en SELECT, WHERE, ORDER BYSolo en WHERE

Comprobar

Empezar la lección de nuevo

Lecciones relacionadas

• Se basa en: NULL: sin valor · ORDER BY, LIMIT y DISTINCT · Aritmética en una consulta · Funciones de texto
• Continúa en: Funciones de agregación: COUNT, SUM, AVG · Muchas tablas, y una tabla unida consigo misma · Un informe por pasos: WITH · Árboles y secuencias: WITH RECURSIVE
• Ejercicios sobre el tema: Expresiones y funciones