Cómo usar la función Coalesce de SQL Server para trabajar con valores NULL

Resumen: en este tutorial, aprenderás cómo usar la función COALESCE de SQL Server para tratar los valores NULL en las consultas.

Introducción a la función COALESCE de SQL Server

La función COALESCE de SQL Server acepta varios argumentos, los evalúa en secuencia y devuelve el primer argumento no nulo.

A continuación, se ilustra la sintaxis de la función COALESCE:

COALESCE(e1,[e2,...,xn])

En esta sintaxis, e1, e2,... xn son expresiones escalares que se evalúan como valores escalares. La función COALESCE devuelve la primera expresión no nula. Si todas las expresiones se evalúan como NULL, entonces la función COALESCE devuelve NULL;

Debido a que COALESCE es una función, puedes usarla en cualquier cláusula que acepte una expresión como SELECT, WHERE, GROUP BY y HAVING.

Ejemplos del uso de la función COALESCE de SQL Server

Veamos ejemplos prácticos de uso de la función COALESCE

A) Uso de la función COALESCE de SQL Server con ejemplo de datos de cadena de caracteres

El siguiente ejemplo usa la función COALESCE para devolver la cadena 'Hi' porque es el primer argumento no nulo:

SELECT
    COALESCE(NULL, 'Hi', 'Hello', NULL) result;

Resultado:

result
------
Hi

(1 row affected)

B) Uso de la función COALESCE de SQL Server con datos numéricos

Este ejemplo usa la función COALESCE para evaluar una lista de argumentos y devolver el primer número:

SELECT
    COALESCE(NULL, NULL, 100, 200) result;

Resultado:

result
-----------
100

(1 row affected)

C) Uso de la función COALESCE de SQL Server para sustituir NULL por nuevos valores

Consulta la siguiente tabla sales.customers de la base de datos de muestra.

La siguiente consulta devuelve el nombre, el apellido, el teléfono y el correo electrónico de todos los clientes:

SELECT
    first_name,
    last_name,
    phone,
    email
FROM
    sales.customers
ORDER BY
    first_name,
    last_name;

Resultado:

La columna de teléfono tendrá NULL si el cliente no tiene el número de teléfono registrado en la tabla sales.customers.

Para que el resultado sea más amigable, puedes usar la función COALESCE para sustituir NULL por la cadena N/A (no disponible) como se muestra en la siguiente consulta:

SELECT
    first_name,
    last_name,
    COALESCE(phone,'N/A') phone,
    email
FROM
    sales.customers
ORDER BY
    first_name,
    last_name;

Resultado:

D) Usar la función COALESCE de SQL Server para usar los datos disponibles

Primero, crea una nueva tabla llamada salaries que almacene los salarios de los empleados:

CREATE TABLE salaries (
    staff_id INT PRIMARY KEY,
    hourly_rate decimal,
    weekly_rate decimal,
    monthly_rate decimal,
    CHECK(
        hourly_rate IS NOT NULL OR
        weekly_rate IS NOT NULL OR
        monthly_rate IS NOT NULL)
);

Cada empleado puede tener solo un salario, ya sea por hora, semanal o mensual.

En segundo lugar, inserta algunas filas en la tabla de salaries:

INSERT INTO
    salaries(
        staff_id,
        hourly_rate,
        weekly_rate,
        monthly_rate
    )
VALUES
    (1,20, NULL,NULL),
    (2,30, NULL,NULL),
    (3,NULL, 1000,NULL),
    (4,NULL, NULL,6000);
    (5,NULL, NULL,6500);

Tercero, consulte los datos de la tabla salaries:

SELECT
    staff_id,
    hourly_rate,
    weekly_rate,
    monthly_rate
FROM
    salaries
ORDER BY
    staff_id;

Resultado:

Cuarto, calcular el salario mensual para cada personal utilizando la función COALESCE como se muestra en la siguiente consulta:

SELECT
    staff_id,
    COALESCE(
        hourly_rate*22*8,
        weekly_rate*4,
        monthly_rate
    ) monthly_salary
FROM
    salaries;

Resultado:

En este ejemplo, usamos la función COALESCE para regresar solo el valor no NULL que se encuentra en las columnas hourly_rate, weekly_rate y monthly_rate.

COALESCE frente a CASE

Las siguientes expresiones devuelven el mismo resultado:

COALESCE(e1,e2,e3)

CASE
    WHEN e1 IS NOT NULL THEN e1
    WHEN e2 IS NOT NULL THEN e2
    ELSE e3
END

"Ten en cuenta que el optimizador de consultas puede usar CASE para reescribir la función COALESCE."

En este tutorial, aprendiste a usar la función COALESCE de SQL Server para manejar valores NULL en consultas.