Con la tecnología creciendo y evolucionando, es esencial mantenerse actualizado con las últimas tendencias como desarrollador. Ya sea un principiante o un experto, una sólida comprensión de la manipulación de cadenas le ayudará a preparar los datos (por ejemplo, generando un formulario diferente del existente, haciéndolo utilizable para su negocio) y a gestionarlos utilizando las funciones incorporadas del servidor SQL.

Además de la manipulación de datos, puede examinar conjuntos de datos, evaluar valores de datos y codificarlos o descodificarlos para obtener datos más significativos. Como resultado, esto le ayuda a navegar por los valores que faltan en los conjuntos de datos, comprender su impacto en los cálculos y agilizar el proceso general de trabajo con los datos para evitar los valores nulos que pueden arruinar los resultados de las operaciones.

Esta guía le guía a través de la función coalesce en SQL, que ayuda a construir programas complejos. El post asume que usted ha conocido y trabajado con SQL y que sólo busca reforzar su comprensión de esta función en particular. Nuestra serie de guías SQL puede ayudarle a empezar rápidamente.

¿Qué es COALESCE () en SQL y sus usos?

La función coalesce en SQL evalúa los parámetros (argumentos) en un orden especificado, como las listas, y devuelve el primer valor no nulo. En pocas palabras, la función evalúa su lista secuencialmente y termina en la instancia del primer valor no nulo. Si todos los argumentos de la lista son nulos, la función devuelve NULL.

Además, la función es inclusiva y compatible con otras bases de datos como MYSQL, Azure SQL Database, Oracle y PostgreSQL.

Puede utilizar Coalesce en los siguientes casos cuando:

  • Manejando valores NULL.
  • Ejecutando varias consultas como una sola.
  • Evitando largas sentencias CASE que consumen mucho tiempo.

Cuando se utiliza en lugar de las sentencias CASE (o de la función ISNULL), coalesce toma muchos parámetros, a diferencia de CASE, que sólo toma dos. Este enfoque le permite escribir menos código y facilita el proceso de escritura.

He aquí la sintaxis:

COALESCE(valorUno, valorDos, valorTres, ..., valorX);

Coalesce en el servidor SQL tiene varias propiedades, incluyendo argumentos del mismo tipo de datos, aceptando muchos parámetros, y argumentos del tipo entero para ser cascados por una función yield para devolver un entero como salida.

Lea también: Hoja de trucos SQL definitiva para marcar más adelante

Pero antes de entrar en cómo utilizar coalesce, entendamos NULL.

¿Qué es un valor NULL en SQL?

El marcador único NULL en SQL indica la inexistencia de un valor en la base de datos. Puede pensar en él como un valor indefinido o desconocido. Por favor, no caiga en el error de pensar en él como una cadena vacía o un valor cero; es la ausencia de un valor. La aparición de un valor nulo en las columnas de una tabla representa la falta de información.

En un caso de uso práctico, la columna de datos de una base de datos de un sitio web de comercio electrónico puede rellenarse con un valor NULL si un cliente no proporciona su id. Null en SQL es único; es un estado, a diferencia de otros lenguajes de programación en los que significa«no apunta a un objeto concreto«.

Los valores NULL en SQL tienen un impacto significativo en las bases de datos relacionales. En primer lugar, le permiten excluir determinados valores mientras trabaja con otras funciones internas. Por ejemplo, puede generar una lista de pedidos totales en un entorno de producción, pero aún quedan otros por completar. Utilizar NULL como marcador de posición permite que la función interna SUM sume los totales.

Además, considere los casos en los que necesite generar la media utilizando la función AVG. Si trabaja con valores cero, los resultados estarán sesgados. En cambio, la base de datos puede eliminar esos campos y utilizar NULL, con lo que se obtienen resultados precisos.

Los valores NULL no tienen inconvenientes. Se consideran valores de longitud variable, siendo bytes o varios de ellos. Como la base de datos deja espacio para estos bytes si exceden lo almacenado en la base de datos, el resultado es que su base de datos ocupa más espacio en el disco duro en comparación con el uso de valores regulares.

Además, cuando trabaje con algunas funciones, tendrá que personalizarlas para eliminar los NULLS. Esto, como resultado, hace que sus procedimientos SQL sean más largos.

Manejo de valores nulos con COALESCE ()

Los valores nulos implican que podría tener un valor, pero no sabe cuál debería ser. Hasta que recopile datos que llenen sus campos con valores reales, los valores NULL son los procuradores.

Aunque puede utilizar valores NULL para varios tipos de datos de su base de datos, incluidos decimales, cadenas, blobs y enteros, es una buena práctica evitarlos cuando se trata de datos numéricos.

El inconveniente es que cuando se utilizan para valores numéricos, probablemente necesitará aclaraciones a medida que desarrolle el código que trabaja con los datos. Más adelante hablaremos de ello.

Las diferentes formas en que se puede utilizar COALESCE () para manejar el valor NULL:

Uso de COALESCE () para sustituir valores nulos por un valor específico

Puede utilizar COALESCE () para devolver valores específicos para todos los valores nulos. Por ejemplo, puede tener una tabla llamada «empleados» con una columna «salario», que puede contener valores nulos si no se ha abonado el salario de los empleados. Por lo tanto, al realizar algunos cálculos, es posible que desee trabajar con un valor específico, cero en este caso, para todas las entradas NULL. He aquí cómo hacerlo.

SELECCIONE COALESCE(salario, 0) COMO salario_ajustado
FROM empleados;

Uso de COALESCE () para seleccionar el primer valor no nulo de varias opciones

A veces, puede que desee trabajar con los primeros valores no nulos de una lista de expresiones. En estos casos, suele tener varias columnas con datos relacionados y querrá dar prioridad a sus valores no nulos. La sintaxis sigue siendo

COALESCE (expresión1, expresión2, ...)

En un caso práctico, suponga que tiene una tabla de contactos con las columnas nombre_preferido y nombre_completo. Y querría generar una lista de contactos lado a lado con sus nombres preferidos (si están disponibles) o sus nombres completos. He aquí cómo abordarlo.

SELECCIONE COALESCE(nombre_preferido, nombre_completo) COMO nombre_visualizado
FROM contactos.

Si el nombre_preferido no es NULL para este caso de prueba, se devolverá. En caso contrario, se devolverá el nombre completo como nombre para mostrar.

Concatenación de cadenas con SQL Coalesce

Puede encontrar problemas con SQL al concatenar cadenas si hay valores nulos implicados. En tales casos, se devuelve NULL como resultado no deseado. Ahora que NULL no es nuestro resultado deseado, puede solucionar el problema utilizando la función coalesce. A continuación se muestra un ejemplo.

Una simple concatenación de cadenas se realiza mediante:

SELECT 'Hola, ¿dónde estás, '|| 'Juan '||? AS ejemplo

El código devuelve:

Ejemplo
Hola, ¿dónde estás, John?

Sin embargo, si utiliza un valor NULL, como se muestra a continuación:

SELECT 'Hola, ¿dónde estás, ' || null | '?' AS ejemplo

La salida es ahora

Ejemplo
NULL

Dado que toda concatenación de cadenas de texto que incluya un valor NULL devuelve NULL, el resultado anterior es NULL. Sin embargo, el problema se resuelve utilizando la función coalesce (). Utilizando esta función, se devuelve una cadena vacía (o un espacio) en lugar de NULL. Por ejemplo, suponga que está listando nombres de coches con sus fabricantes; ésta es su consulta

SELECCIONE 
coche || ', fabricante: ' || COALESCE(fabricante, '-') COMO marca_coche
FROM stock

Si el fabricante es NULL, tendrá el ‘–‘ en lugar de NULL. Estos son los resultados esperados

marca_coche
outlander, fabricante —
flying spurs, fabricante Bentley
royal athlete, fabricante: —
berlina real, fabricante:: Crown

Como puede ver, se eliminan los resultados NULL, con la opción de insertar su valor de cadena de sustitución.

Función SQL Coalesce y pivoteo

El pivoteo SQL es una técnica utilizada para transformar filas en columnas. Le permite transponer (rotar) datos de la forma «normalizada» (con muchas filas y menos columnas) a la «desnormalizada» (menos filas y más columnas). La función coalesce puede utilizarse con el pivoteo SQL para manejar los valores nulos en los resultados pivotados.

Cuando pivotea en SQL, transforma filas en columnas; las columnas resultantes son funciones agregadas de algunos datos. Si, en algún caso, una agregación da como resultado un valor nulo para una celda concreta, puede utilizar `COALESCE` para sustituir los valores nulos por un valor por defecto o una representación significativa. A continuación se muestra un ejemplo.

Considere una tabla, ventas, con las columnas año, trimestre e ingresos, y le gustaría pivotar los datos; de tal forma que tenga los años como columnas y la suma de los ingresos de cada trimestre como valores. Pero, algunos trimestres no tienen datos de ingresos, dando valores nulos en el resultado pivotado. En este caso, puede utilizar COALESCE para sustituir los valores nulos en el resultado pivotado por un cero (0).

SELECCIONE
    año
    COALESCE(SUM(CASE WHEN quarter = 'Q1' THEN revenue END), 0) AS Q1_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q2' THEN revenue END), 0) AS Q2_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q3' THEN revenue END), 0) AS Q3_Revenue,
    COALESCE(SUM(CASE WHEN quarter = 'Q4' THEN revenue END), 0) AS Q4_Revenue
DE ventas
GROUP BY año;

Función definida por el usuario escalar y función SQL Coalesce

Puede utilizar UDF escalares y coalesce para realizar una lógica compleja que maneje valores nulos. La combinación de estas funciones le ayudará a conseguir transformaciones de datos y cálculos más sofisticados en las consultas SQL. Considere una tabla, Empleados, con esta estructura.

CREAR TABLA Empleados (
    EmployeeID INT PRIMARY KEY,
    Nombre VARCHAR(50),
    Apellido VARCHAR(50),
    Salario INT,
    Bonificación INT
);

Es posible que desee calcular los ingresos totales de cada empleado (salario más bonificación). Sin embargo, faltan algunos valores. En este caso, su UDF escalar puede manejar las sumas de salario y bonificación, mientras que coalesce maneja los valores nulos. He aquí la UDF escalar para las ganancias totales.

CREAR FUNCIÓN dbo.CalcularGananciasTotales (@sueldo INT, @bonificación INT)
DEVUELVE INT
COMO
BEGIN
    DECLARE @gananciasTotales INT;
    SET @gananciasTotales = @salario COALESCE(@bonus, 0);
    RETURN @totalGanancias;
FIN;
A continuación, puede utilizar la UDF escalar con coalesce en una consulta:
SELECT EmployeeID, FirstName, LastName,
       Salario, Bonificación, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Empleados;

Validación de datos mediante SQL Coalesce

Cuando trabaje con bases de datos, es posible que desee validar valores numéricos. Por ejemplo, supongamos que tiene las columnas nombre_producto, precio y descuento en una tabla, productos. Quiere recuperar los nombres de producto, precios y descuentos de cada artículo. Pero, le gustaría tratar todos los valores de descuento NULL como 0. La función coalesce puede ser útil. He aquí cómo utilizarla.

SELECT nombre_producto, precio, COALESCE(descuento, 0) COMO descuento 
FROM productos

SQL Coalesce y columnas calculadas

Las columnas computadas son columnas virtuales calculadas a partir de expresiones u otras columnas de una tabla. Dado que las columnas computadas no se almacenan físicamente en la base de datos, puede aprovecharlas con la función coalesce cuando maneje escenarios y transformaciones complejas. He aquí un ejemplo práctico de uso.

Considere una tabla `productos` con las columnas `precio`, `descuento` y `tasa_impuesto`. En este caso, desea crear una columna computada, `precio_total`, para representar el precio final del producto tras aplicar el descuento y el impuesto. Si no se especifica ni el descuento ni el impuesto (NULL), querrá proceder con sus cálculos utilizando un cero. He aquí cómo aprovechar coalesce para adaptar la operación.

CREAR TABLA productos(
precio DECIMAL(10, 2),
descuento DECIMAL(10, 2),
tax_rate DECIMAL(5, 2),
precio_total AS (COALESCE(precio, 0) - COALESCE(precio*descuento, 0))* COALESCE(1 tipo_impuesto, 1)
);

En el código anterior, esto es lo que ocurre

  1. La columna computada precio_total se define como (COALESCE(precio, 0) - COALESCE(precio*descuento, 0))* COALESCE(1 tasa_impuestos, 1) .
  2. Si el precio es NULO, COALESCE(precio*descuento, 0 ) garantiza que se trate como 0.
  3. Si el descuento es nulo, COALESCE( precio*descuento ) asegura que se trata como 0, y la multiplicación no afecta al cálculo.
  4. Si el tipo_impuesto es NULO, COALESCE( 1 tipo_impuesto, 1 ) asegura que se trata como 0, lo que significa que no se aplica ningún impuesto, y la multiplicación no afecta al cálculo.

La configuración anterior permite generar el precio_total, una columna computada, con el precio final real, a pesar de que falten valores o éstos sean NULL.

Coalescencia SQL y expresión CASE

Puede utilizar sintácticamente coalesce a través de la expresión CASE. He aquí un ejemplo:

SELECT
Productname ' ' deliverydate productdetails,
distribuidor,
CASE
WHEN cellphone is NOT NULL Then cellphone
WHEN workphone is NOT NULL Then workphone
ELSE 'NA'
END
EmergencyContactNumber
DESDE
dbo.tb_EmergencyContact

En la configuración anterior, las consultas CASE son como la función COALESCE.

Además, es posible utilizar las expresiones COALESCE y CASE en la misma consulta. Las dos técnicas pueden manejar valores NULL y aplicar lógica condicional simultáneamente. Ilustremos esto con un ejemplo.

Considere un caso en el que tiene una tabla, productos con las columnas id_producto, nombre_producto, precio y descuento. Algunos de sus productos tienen un descuento específico, mientras que otros no. Si un producto tiene un descuento, querrá mostrar el precio con descuento; en caso contrario, deberá mostrar el normal.

SELECCIONE 
    producto_id,
    nombre_producto,
    precio,
    COALESCE(
        CASE
            WHEN descuento > 0 THEN precio - (precio * descuento / 100)
            ELSE NULL
        END
        precio
    ) AS precio_con_descuento
FROM productos;

En el código anterior, la función `CASE` comprueba si el `descuento` es mayor que cero y calcula el precio con descuento, de lo contrario devuelve un valor NULL. La función `COALESCE` toma como parámetros el resultado de `CASE` y `price`. Devuelve el primer valor no NULL, devolviendo efectivamente el precio con descuento si está disponible o el precio normal si no lo hay.

Palabras finales

Este post ha demostrado varias formas de utilizar la función `COALESCE` en sus consultas a la base de datos. Al evaluar los parámetros en un orden especificado y devolver el primer valor no NULL, la función coalesce simplifica las consultas haciéndolas eficientes.

Coalesce es una función versátil tanto si maneja valores nulos, concatenación de cadenas, pivoteo de datos, validación o trabaja con columnas computadas. Al dominar la función coalesce, los desarrolladores pueden navegar por los datos que faltan y crear diseños de bases de datos sin errores. Recuerde que, para dominar la técnica, es posible que necesite algo más de práctica en profundidad.

Ahora puede consultar cómo crear restricciones de clave foránea en SQL.