La función COALESCE en SQL: sintaxis y ejemplos

COALESCE es una función que recibe una lista de argumentos y devuelve el primero que no es NULL. Su uso más habitual es poner un valor por defecto en lugar de NULL en los resultados de una consulta.

MySQL 8.4
COALESCE(valor_1, valor_2, ..., valor_N)

Funciona igual en todos los SGBD populares: MySQL, PostgreSQL, SQLite, SQL Server y Oracle. Forma parte del estándar SQL, y por eso conviene elegir COALESCE en lugar de las funciones no estándar IFNULL, ISNULL y NVL. Hablaremos de ellas más abajo.

Cómo funciona COALESCE

La función revisa los argumentos de izquierda a derecha y se detiene en el primero que no es NULL:

MySQL 8.4
SELECT COALESCE(NULL, NULL, 'SQL Academy', 'valor de reserva') AS result;

Los dos primeros argumentos son NULL, así que se saltan. El tercero no es NULL: se convierte en el resultado, y la comprobación ya no llega a la cadena 'valor de reserva'.

Si todos los argumentos son NULL, el resultado también será NULL.

Ejemplo en vivo: sustituir NULL en un informe

Echemos un vistazo a la tabla orders de la base de datos de un servicio de reparto. La columna promo_code guarda el código promocional del pedido, y si el pedido se hizo sin código, ahí hay un NULL:

MySQL 8.4
SELECT order_id, total_amount, promo_code
FROM orders
WHERE order_id IN (1, 4, 8, 13, 24)
ORDER BY order_id;

En un informe para un responsable, las celdas vacías parecen un error de exportación. Pongamos un texto comprensible en lugar de NULL:

MySQL 8.4
SELECT order_id, total_amount, COALESCE(promo_code, 'sin código promocional') AS promo
FROM orders
WHERE order_id IN (1, 4, 8, 13, 24)
ORDER BY order_id;

Donde hay código promocional, COALESCE lo ha devuelto tal cual. Donde había NULL, ha puesto el texto del segundo argumento. El bloque de arriba es interactivo: cambia el texto de sustitución por el tuyo o envuelve en COALESCE la columna delivery_time_min y mira qué cambia.

Una cadena de alternativas

La fuerza de COALESCE está en que admite tantos argumentos como quieras. El caso clásico es elegir el primer contacto disponible de un cliente: el teléfono del trabajo; si no lo hay, el móvil; y si tampoco, un texto de relleno:

MySQL 8.4
SELECT name, COALESCE(work_phone, mobile_phone, 'sin contacto') AS contact
FROM clients;

Cada fila pasa la comprobación de izquierda a derecha independientemente de las demás: a Ana le ha tocado el teléfono del trabajo, a Boris el móvil y a Vera el texto de relleno.

COALESCE con funciones de agregación

Las funciones de agregación SUM, AVG, MIN y MAX devuelven NULL si en el grupo no hay ni un solo valor. Calculemos el tiempo medio de entrega según el estado del pedido:

MySQL 8.4
SELECT status, ROUND(AVG(delivery_time_min)) AS avg_delivery
FROM orders
GROUP BY status
ORDER BY status;

Los pedidos cancelados no tienen tiempo de entrega, porque nunca se entregaron, así que AVG ha devuelto NULL. Si este resultado se usa en otros cálculos o en un gráfico, el NULL los romperá. Envolvemos el agregado en COALESCE:

MySQL 8.4
SELECT status, COALESCE(ROUND(AVG(delivery_time_min)), 0) AS avg_delivery
FROM orders
GROUP BY status
ORDER BY status;

Es importante entender qué significa la sustitución: aquí el cero quiere decir «no hay datos», no «se entregó al instante». En los informes donde esa diferencia es crítica, es mejor dejar el NULL o mostrar esos grupos por separado.

NULLIF: la función opuesta

NULLIF hace lo contrario: convierte un valor en NULL. Recibe exactamente dos argumentos y devuelve NULL si son iguales; si no, devuelve el primero:

MySQL 8.4
NULLIF(valor_1, valor_2)

MySQL 8.4
SELECT NULLIF('delivered', 'canceled') AS a, NULLIF('canceled', 'canceled') AS b;

Protección contra la división entre cero

El uso más frecuente de NULLIF es en el denominador de una fracción. En PostgreSQL, dividir entre cero hace que la consulta falle:

MySQL 8.4
SELECT 100 / 0 AS result;

MySQL, en la misma situación, devolverá NULL sin decir nada y generará una advertencia. NULLIF hace que el comportamiento sea igual y predecible en ambos SGBD:

MySQL 8.4
SELECT 100 / NULLIF(0, 0) AS result;

El denominador se ha convertido en NULL, la división entre NULL ha dado NULL y la consulta no ha fallado. Si envuelves la expresión en COALESCE, puedes devolver, por ejemplo, un cero.

NULLIF y COALESCE juntas: limpiar cadenas vacías

En los datos reales, además de NULL, suelen aparecer cadenas vacías y cadenas formadas solo por espacios. COALESCE no las sustituye, porque una cadena vacía no es NULL. En nuestra tabla orders tenemos justo ese caso:

MySQL 8.4
SELECT order_id, promo_code, COALESCE(promo_code, 'sin código promocional') AS promo
FROM orders
WHERE order_id IN (1, 2, 3, 8, 24)
ORDER BY order_id;

El pedido 1 está resuelto, pero los pedidos 2 y 3, con espacios y con una cadena vacía, siguen siendo «agujeros» en el informe. La solución es combinar tres funciones: TRIM quita los espacios, NULLIF convierte la cadena vacía en NULL y COALESCE pone el texto de relleno:

MySQL 8.4
SELECT order_id, COALESCE(NULLIF(TRIM(promo_code), ''), 'sin código promocional') AS promo
FROM orders
WHERE order_id IN (1, 2, 3, 8, 24)
ORDER BY order_id;

Esta combinación es el caballo de batalla de la limpieza de datos: apúntatela.

¿Qué devuelve la expresión COALESCE(NULL, '', 'SQL Academy')?

COALESCE vs IFNULL vs ISNULL vs NVL

COALESCE tiene parientes no estándar ligados a SGBD concretos:

La trampa principal aquí es ISNULL. En SQL Server sustituye NULL y tiene dos argumentos, mientras que en MySQL la función con el mismo nombre recibe un solo argumento y simplemente comprueba si es NULL, devolviendo 1 o 0:

MySQL 8.4
SELECT ISNULL(NULL) AS is_null, ISNULL('texto') AS is_not_null, IFNULL(NULL, 'sustituto') AS replaced;

Un código con ISNULL(a, b) llevado de SQL Server a MySQL fallará con un error por número de argumentos incorrecto.

En PostgreSQL no existen ni IFNULL ni ISNULL, y una consulta que las use terminará con un error:

MySQL 8.4
SELECT IFNULL(promo_code, 'sin código promocional') FROM orders;

Aquí la única forma de sustituir NULL es COALESCE, y ese es un motivo más para usarla desde el principio.

La conclusión es sencilla: si tu código puede acabar en otro SGBD, escribe COALESCE y funcionará en todas partes.

¿Qué función para sustituir NULL funciona tanto en MySQL como en PostgreSQL y SQL Server?

Dónde COALESCE se comporta de forma inesperada

Tipos de argumentos incompatibles. PostgreSQL exige que todos los argumentos se puedan convertir a un mismo tipo. Si intentas poner una cadena en lugar de un NULL numérico, obtendrás un error:

MySQL 8.4
SELECT COALESCE(delivery_time_min, 'sin datos') FROM orders;

MySQL, en la misma situación, convertirá el número en cadena sin avisar: la consulta funcionará, pero la columna cambiará de tipo, y eso puede sorprender a la aplicación que la lee.

COALESCE no sustituye a IS NULL en las condiciones. La comparación WHERE promo_code = NULL no devolverá ninguna fila, y COALESCE no sirve aquí: para filtrar por NULL existe el operador IS NULL. Más detalles en la lección Operadores IS NULL, BETWEEN, IN.

Una cadena vacía no es NULL. COALESCE('', 'sustituto') devolverá la cadena vacía, no el sustituto. Si en tus datos «vacío» significa «sin valor», usa la combinación NULLIF y COALESCE.

Siguientes pasos

La mejor forma de afianzar COALESCE es usarla tú mismo:

• resuelve ejercicios de SQL en la Práctica SQL: las funciones para trabajar con NULL aparecen allí a menudo;
• descubre cómo se comporta NULL en comparaciones y filtros en la lección IS NULL, BETWEEN, IN;
• tienes fichas breves de cada función en la Referencia de funciones SQL: COALESCE, IFNULL, NULLIF.