COALESCE devuelve el primer valor de su lista que no sea NULL. Así es como SQL cambia un valor faltante por uno de respaldo. Los ejemplos funcionan en PostgreSQL, MySQL, SQLite y SQL Server.
Cómo usar COALESCE
Pásale a COALESCE la cantidad de valores que quieras. Los revisa de izquierda a derecha y devuelve el primero que no sea NULL, o NULL si todos lo son:
COALESCE(value1, value2, ...)
SELECT COALESCE(NULL, 'second', 'third');
-- Output: second
SELECT COALESCE(NULL, NULL);
-- Output: NULL
En una consulta real, los valores suelen ser columnas, con un literal al final como último recurso:
-- Input: users
-- id | name | nickname
-- ---+--------------+---------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | NULL
-- 3 | Priya Shah | P
SELECT id, name, COALESCE(nickname, name) AS display_name
FROM users;
-- Output:
-- id | name | display_name
-- ---+--------------+-------------
-- 1 | Maria Garcia | Mia
-- 2 | Tom Becker | Tom Becker
-- 3 | Priya Shah | P
Reglas que debes conocer
- Se detiene en el primer valor que no sea NULL, así que los argumentos siguientes ya no se evalúan una vez que encuentra uno. Pon primero el valor que más seguido viene lleno.
- Todos los argumentos necesitan un tipo compatible. Mezclar un número con texto falla en PostgreSQL y SQL Server, así que primero haz un cast:
-- Input: products
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | NULL
SELECT name, COALESCE(stock, 'N/A') AS stock FROM products;
-- PostgreSQL: ERROR: invalid input syntax for type integer: "N/A"
-- SQL Server: Conversion failed when converting the varchar value 'N/A' to data type int.
-- (in MySQL, cast with CAST(stock AS CHAR))
SELECT name, COALESCE(CAST(stock AS VARCHAR(10)), 'N/A') AS stock FROM products;
-- Output:
-- name | stock
-- -------------+------
-- Desk Lamp | 12
-- Office Chair | N/A
-
Una cadena vacía no es NULL. COALESCE deja pasar
''tal cual. Envuelve la columna enNULLIF(column, '')para tratar también las cadenas vacías como faltantes:
SELECT COALESCE('', 'No name');
-- Output: '' (an empty string)
SELECT COALESCE(NULLIF('', ''), 'No name');
-- Output: No name
Sintaxis alternativa
También vas a ver atajos de dos argumentos: IFNULL en MySQL y SQLite, e ISNULL en SQL Server:
SELECT IFNULL(NULL, 'fallback');
-- Output: fallback
Mejor usa COALESCE. Acepta cualquier cantidad de argumentos, y el ISNULL de SQL Server le da a su resultado el tipo del primer argumento, lo que puede recortar tu valor de respaldo:
-- SQL Server
DECLARE @code VARCHAR(2) = NULL;
SELECT ISNULL(@code, 'none') AS with_isnull, COALESCE(@code, 'none') AS with_coalesce;
-- Output:
-- with_isnull | with_coalesce
-- ------------+--------------
-- no | none
Una expresión CASE puede hacer lo mismo, pero con más texto: COALESCE(nickname, name) es una forma corta de CASE WHEN nickname IS NOT NULL THEN nickname ELSE name END. Consulta la guía de la sentencia CASE en SQL para cuando necesites más poder.
¿Cuándo deberías usar COALESCE?
Usa COALESCE siempre que un NULL deje tu resultado incorrecto, feo o vacío. La mayoría de los usos caen en uno de estos cuatro.
Recurrir a otra columna
Cuando el valor que quieres está en una de varias columnas, COALESCE elige la primera que esté llena: un apodo o un nombre completo, un celular o un teléfono fijo, una dirección de envío o de facturación.
Usar un valor predeterminado cuando falta todo
Pon un literal al final de la lista y el resultado nunca será NULL. Eso es lo que quieres en reportes y pantallas, donde “Dirección desconocida” o 0.00 se lee mejor que un espacio en blanco.
Llenar huecos con un valor calculado
El valor de respaldo no tiene que ser una columna o una constante. Puede ser una expresión, como un nombre de usuario o un correo armado a partir de otras columnas, lo que hace que COALESCE sea muy útil para limpiar datos importados.
Mantener los NULL fuera de los cálculos
Cualquier operación aritmética con un NULL da NULL, así que un descuento o bono faltante arruina todo el total. COALESCE(discount, 0) convierte el valor faltante en un número que el cálculo puede usar.
Ejemplos reales de COALESCE
Mostrar un precio, el que esté registrado
Los productos pueden tener un precio de oferta, un precio normal o (antes del lanzamiento) ninguno. Revísalos en orden y usa cero por defecto:
-- Input: products
-- product_id | name | sale_price | regular_price
-- -----------+---------------+------------+--------------
-- 1 | Desk Lamp | 19.99 | 24.99
-- 2 | Office Chair | NULL | 149.00
-- 3 | Standing Desk | NULL | NULL
SELECT product_id, name, COALESCE(sale_price, regular_price, 0.00) AS price
FROM products;
-- Output:
-- product_id | name | price
-- -----------+---------------+-------
-- 1 | Desk Lamp | 19.99
-- 2 | Office Chair | 149.00
-- 3 | Standing Desk | 0.00
Elegir el mejor número para llamar
Los contactos tienen hasta tres números. Muestra el primero disponible e indícalo cuando no haya ninguno:
-- Input: contacts
-- name | primary_phone | mobile_phone | work_phone
-- -----+---------------+--------------+-----------
-- Ada | 555-0101 | 555-0199 | NULL
-- Bo | NULL | 555-0142 | 555-0177
-- Cy | NULL | NULL | 555-0123
-- Di | NULL | NULL | NULL
SELECT name, COALESCE(primary_phone, mobile_phone, work_phone, 'No phone') AS phone
FROM contacts;
-- Output:
-- name | phone
-- -----+---------
-- Ada | 555-0101
-- Bo | 555-0142
-- Cy | 555-0123
-- Di | No phone
Generar un correo electrónico provisional
A los empleados sin correo registrado se les arma uno con su nombre, para que cada fila del directorio tenga un valor:
-- Input: employees
-- employee_id | first_name | last_name | email
-- ------------+------------+-----------+-----------------
-- 1 | Grace | Hopper | ghopper@navy.mil
-- 2 | Alan | Turing | NULL
SELECT
employee_id,
first_name || ' ' || last_name AS full_name,
COALESCE(
email,
LOWER(first_name) || '.' || LOWER(last_name) || '@company.com'
) AS email
FROM employees;
-- Output:
-- employee_id | full_name | email
-- ------------+--------------+------------------------
-- 1 | Grace Hopper | ghopper@navy.mil
-- 2 | Alan Turing | alan.turing@company.com
En MySQL y SQL Server, une las cadenas con CONCAT() en lugar de ||.
Calcular el total de un pedido con descuentos opcionales
Sin COALESCE, una sola línea sin descuento hace que el total del pedido salga mal, porque SUM se salta por completo las líneas con NULL:
-- Input: order_items
-- order_id | product | quantity | unit_price | discount
-- ---------+----------+----------+------------+---------
-- 101 | Keyboard | 1 | 49.00 | 5.00
-- 101 | Mouse | 2 | 19.50 | NULL
-- 102 | Monitor | 1 | 189.00 | NULL
-- 102 | Cable | 3 | 6.00 | 2.00
-- Wrong: lines with a NULL discount drop out of the total
SELECT order_id, SUM(quantity * unit_price - discount) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+------
-- 101 | 44.00
-- 102 | 16.00
-- Right: a missing discount counts as 0
SELECT order_id, SUM(quantity * unit_price - COALESCE(discount, 0)) AS total
FROM order_items
GROUP BY order_id
ORDER BY order_id;
-- Output:
-- order_id | total
-- ---------+-------
-- 101 | 83.00
-- 102 | 205.00
Más lecturas sobre COALESCE
En este blog:
-
COALESCE en MySQL: COALESCE junto al
IFNULLde MySQL - Guía de la sentencia CASE en SQL: lógica condicional más allá de revisar NULL
- Guía de “distinto de” en SQL: por qué las comparaciones con NULL nunca coinciden
- Guía de GROUP BY en SQL: las agregaciones a las que COALESCE suele alimentar
- Guía de SUBSTRING en SQL: extraer texto de columnas que pueden ser NULL
Documentación oficial:
Beekeeper Studio es un cliente de bases de datos gratuito y de código abierto
La mejor herramienta de consultas y edición de SQL que he usado. Tiene todo lo que necesito para administrar mi base de datos. - ⭐⭐⭐⭐⭐ Mit
Beekeeper Studio es rápido, intuitivo y fácil de usar. Es compatible con muchísimas bases de datos y funciona de maravilla en Windows, Mac y Linux.
Lo que dicen los usuarios de Beekeeper Studio
"Beekeeper Studio reemplazó por completo mi antiguo flujo de trabajo con SQL. Es rápido, intuitivo y hace que trabajar con bases de datos vuelva a ser agradable."
"He probado muchas GUI de bases de datos, pero Beekeeper logra el equilibrio perfecto entre funciones y simplicidad. Simplemente funciona."