Cómo utilizar la función COALESCE() en SQL

Los usuarios de SQL se encuentran a menudo con valores NULL en sus consultas y necesitan procesarlos adecuadamente. La función COALESCE() ayuda a manejar los valores NULL. Lee este artículo para aprender a utilizar COALESCE() en tus consultas.

Las tablas SQL almacenan datos en registros, y los registros están compuestos por campos. Puede haber situaciones en las que no conozcamos el valor de un campo específico. Por ejemplo, supongamos que tenemos una tabla con los datos de persons. Tiene los campos first_name, last_name, y marital_status. Cuando no conocemos el marital_status para una persona determinada, SQL nos permite asignar un valor NULL para este campo. Sin embargo, el valor NULL no significa que la persona no tenga un marital_status; sólo significa que "no conocemos ese valor".

En otras palabras, SQL utiliza los valores NULL para representar la ausencia de valor. Sin embargo, los NULL pueden ser difíciles de manejar. Por eso SQL ha incluido la función COALESCE(), de la que hablaremos en este artículo.

Antes de comenzar con los detalles técnicos de NULL y COALESCE(), le sugiero que consulte nuestro curso interactivo sobre Funciones estándar de SQL. Contiene una revisión completa de los valores NULL, las funciones relacionadas con NULL y otras funciones comunes en SQL.

¿Qué hace COALESCE()?

En las bases de datos SQL, cualquier tipo de datos admite NULL como valor válido; es decir, cualquier columna puede tener un valor NULL, independientemente del tipo de datos que sea. (Obviamente, algunas columnas serán obligatorias (no anulables), pero esto lo establece el diseñador de la base de datos, no el tipo de datos en sí). Veamos un ejemplo sencillo con la tabla persons.

Podemos utilizar la función COALESCE() de SQL para sustituir el valor NULL por un texto simple:

SELECT 	
  first_name, 
  last_name, 
  COALESCE(marital_status,'Unknown')
FROM persons

En la consulta anterior, la función COALESCE() se utiliza para devolver el valor 'Unknown' sólo cuando marital_status es NULL. Cuando marital_status no es NULL, COALESCE() devuelve el valor de la columna marital_status. En otras palabras, COALESCE() devuelve el primer argumento no NULL.

Conozca los datos del ejemplo

Para el resto del artículo, demostraremos la función COALESCE() utilizando la tabla stockque se muestra a continuación.

Esta tabla almacena registros de datos de productos para un mercado e incluye las columnas product, brand, subcategory, category, family, units, quantity_available (el stock actual de este producto), y minimum_to_have (el umbral cuando el mercado necesita pedir este producto a sus proveedores).

Observará que algunos de los productos tienen una subcategoría, pero otros no. Por ejemplo, el producto "costillas de cerdo" pertenece a la subcategoría "carne de cerdo" en la categoría "carne" y la familia "alimentos". El producto "tomates" pertenece a la categoría "verduras" y a la familia "alimentos"; no tiene subcategoría, por lo que hay un NULL en este campo.

Ejemplo de consulta SQL con la función COALESCE()

Veamos ahora cómo utilizar la función COALESCE() en algunos ejemplos realistas.

Ejemplo 1: Usar COALESCE() para reemplazar NULL con una etiqueta

Queremos mostrar todos los productos con su subcategoría, categoría y familia. Sin embargo, hay algunos productos con un NULL en su categoría o subcategoría. Para estos productos, queremos mostrar un texto: 'No Category' o 'No Subcategory'. Esta es la consulta que utilizaríamos:

SELECT product, 
  COALESCE(subcategory,'No Subcategory') AS subcategory,
  COALESCE(category,'No Category') AS category,
  COALESCE(family,'No Family') AS family
FROM stock

Utilizamos la función COALESCE() para sustituir los valores NULL por un texto. Puedes ver el resultado a continuación:

Ejemplo 2: Utilizar COALESCE() al concatenar NULL y cadenas

Un problema frecuente en SQL relacionado con los valores NULL es la concatenación de cadenas. Muchas operaciones que implican valores NULL devuelven un NULL como resultado. Si queremos concatenar dos cadenas y una de ellas es NULL, el resultado de la concatenación será NULL. He aquí una simple concatenación de texto:

SELECT 'Hello, how are you ' || 'Peter ' || '?'  AS example

Devuelve:

Sin embargo, si utilizamos un NULL ...

SELECT 'Hello, how are you ' || null || '?'  AS example

... obtenemos:

El resultado es NULL porque toda concatenación de cadenas de texto que incluya un valor NULL devuelve un valor NULL. Para evitarlo, podemos utilizar la función COALESCE() para devolver una cadena vacía (o un espacio) en lugar de un NULL. Por ejemplo, supongamos que queremos una lista de los nombres de los productos con la marca. Podemos escribir la siguiente consulta:

SELECT 
    product || ', brand: ' || COALESCE(brand, '--') AS product_brand
FROM stock

Cuando una marca es NULL, pondremos un '--' en lugar de un NULL. Observe el resultado:

Ejemplo 3: Utilizar COALESCE() con múltiples argumentos

Puede utilizar la función COALESCE() con más de dos argumentos. Supongamos que queremos un informe que liste los productos y sus subcategorías. Si la subcategoría es NULL, queremos sustituir la subcategoría por la categoría. Y si tanto la subcategoría como la categoría son NULL, queremos sustituirlas por la familia del producto. Veamos la consulta SQL:

SELECT 
   product ||' - '||
   COALESCE(subcategory, category, family, 'no product description ')
   AS product_and_subcategory
FROM stock

Estamos utilizando la función COALESCE() con cuatro argumentos; el primer argumento no NULL será devuelto, como podemos ver en el resultado de abajo:

Para más detalles sobre esto, sugiero el artículo Cómo abordar los NULL de SQL.

Ejemplo 4: Usar COALESCE() para reemplazar NULL con un valor calculado

La función SQL COALESCE() también puede utilizarse para calcular o estimar un valor cuando éste no está presente. Por ejemplo, cada producto tiene un umbral (representado por la columna minimum_to_have) que requiere un nuevo pedido al proveedor. Sin embargo, algunos registros podrían tener un valor NULL en la columna minimum_to_have; en este caso, podemos definir que el umbral será el 50% de la columna quantity_available. La consulta para calcular la estimación del umbral es:

SELECT product,
  quantity_available,
  minimum_to_have,
  COALESCE(minimum_to_have, quantity_available * 0.5) AS threshold
FROM   stock

La función COALESCE() devuelve aquí minimum_to_have cuando el valor minimum_to_have no es NULL. Si minimum_to_have es NULL, entonces COALESCE() devolverá quantity_available * 0.5

Ejemplo 5: Uso de COALESCE() con la cláusula ROLLUP

En el siguiente ejemplo, utilizaremos la cláusula ROLLUP (una extensión de GROUP BY) para obtener la cantidad total de productos que tenemos para cada subcategoría, incluyendo un subtotal de productos para cada categoría y familia. Veamos la consulta:

SELECT family,
 category,
 subcategory,
 SUM(quantity_available) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

La cláusula ROLLUP asume una jerarquía entre las columnas family, category, y subcategory. Así, genera todos los conjuntos de agrupación que tienen sentido considerando la jerarquía: GROUP BY family, GROUP BY family, category y GROUP BY family, category, subcategory. Esta es la razón por la que ROLLUP se utiliza a menudo para generar subtotales y totales generales para los informes.

Veamos los resultados a continuación:

Puede ver algunos NULLs en el resultado anterior. Cada NULL significa que esta columna no estaba presente en el GROUP BY para el cálculo de la cantidad en stock. Por ejemplo, estas filas ...

... son el resultado de la ejecución de GROUP BY family, category. Esta es la razón de tener un NULL bajo la columna subcategory.

En la siguiente consulta, utilizaremos la función COALESCE() para mejorar la legibilidad del informe. Sustituiremos esos valores NULL por un texto que aclare el motivo del NULL:

SELECT COALESCE(family,'All Families') AS family,
 COALESCE(category,'All Categories') AS category,
 COALESCE(subcategory,'All Subcategories') AS subcategory,
 SUM(quantity_available) as quantity_in_stock 
FROM stock
GROUP BY ROLLUP(family, category, subcategory)
ORDER BY family, category, subcategory

El resultado es:

En el resultado anterior, podemos observar como los valores NULL son sustituidos por textos y las filas de los informes con los textos que empiezan por 'All' muestran un subtotal en quantity_in_stock.

Me gustaría sugerir el artículo La función COALESCE de SQL: Handling NULL Values si quiere aprender más sobre esto.

Aprovechar la función COALESCE() para procesar NULLs

En este artículo, demostramos varias formas de utilizar la función SQL COALESCE(). Cubrimos cómo usar COALESCE() para reemplazar valores NULL, cómo calcular un valor alternativo y cómo combinar COALESCE() con la cláusula ROLLUP, entre otros ejemplos.

Antes de terminar, tengo otra recomendación para ti. Nuestra hoja de trucos gratuita Funciones estándar de SQL te permite encontrar rápidamente detalles sobre las funciones incorporadas de SQL, las funciones agregadas y mucho más. La utilizo casi todos los días cuando trabajo con SQL. También puedes probar nuestro Funciones estándar de SQL curso, en el que puedes aprender y practicar funciones SQL numéricas, de texto, de fecha y de manejo de NULL. ¡Aumenta tus habilidades y aumenta tus activos!