SQL Server - ISNULL vs COALESCE: Conociendo las Diferencias entre las Dos Funciones

Un error muy común entre los desarrolladores de SQL es sobre las diferencias entre las funciones ISNULL y COALESCE, donde la gran mayoría de personas cree que es solo el hecho de que la función ISNULL permite solo 1 parámetro de entrada para analizar valores nulos, mientras que la función COALESCE le permite pasar N parámetros de entrada.

Sin embargo, hay otra diferencia muy importante a la que muchas personas terminan por no prestarle atención y que puede hacerte pasar mucho tiempo intentando depurar un problema y es la que te explicaré en este artículo.

Tuve un proceso ETL que dio un error debido a esta diferencia en los tipos de datos entre ISNULL y COALESCE y terminé creando este artículo porque entendí que otras personas podrían terminar perdiendo el tiempo tratando de entender qué sucedió.

Diferencia n.º 1: número de parámetros de función

La primera gran diferencia y la más conocida entre las funciones ISNULL y COALESCE es la cantidad de parámetros que aceptan las funciones.

Creación de la base de pruebas.

IF (OBJECT_ID('tempdb..#Teste1') IS NOT NULL) DROP TABLE #Teste1
CREATE TABLE #Teste1 (
   Coluna1 NUMERIC(10, 2),
   Coluna2 NUMERIC(10, 2),
   Coluna3 NUMERIC(10, 2)
)

INSERT INTO #Teste1
VALUES
    ('255.55', '4755.12', '145.55'),
    (NULL, NULL, NULL),
    (NULL, '4755.12', NULL),
    (NULL, NULL, '145.55')

Ejemplo:

Devuelve el primer valor no nulo de cada fila o 0 si todas las columnas son NULL

-- COM ISNULL
SELECT 
    ISNULL(Coluna1, ISNULL(Coluna2, ISNULL(Coluna3, 0)))
FROM 
    #Teste1


-- COM COALESCE
SELECT
    COALESCE(Coluna1, Coluna2, Coluna3, 0)
FROM
    #Teste1

Resultado:

Como podemos ver en el código anterior, la función ISNULL solo acepta un parámetro de entrada, que puede ser una columna o un valor, y un parámetro para que usted defina el valor de reemplazo si el primer parámetro es nulo. Si quieres hacer comparaciones entre varias columnas/valores, tendrás que utilizar la función varias veces, anidada.

La función COALESCE, por otro lado, acepta varios parámetros de entrada (al menos 2 parámetros y ningún máximo definido), lo que hace que el uso de esta función sea más sencillo que ISNULL.

Diferencia n.º 2: tipo de datos de retorno

Una diferencia que muchas personas terminan sin notar es en relación con el tipo de datos de retorno: mientras que la función ISNULL considera el tipo de datos de la primera columna como el tipo de datos de retorno de la función, la función COALESCE será el mismo tipo de datos del parámetro que será devuelto (el primero no nulo).

En la práctica, este escenario hace que las dos funciones se comporten de manera muy diferente.

Creación de la base de pruebas.

IF (OBJECT_ID('tempdb..#Teste2') IS NOT NULL) DROP TABLE #Teste2
CREATE TABLE #Teste2 (
    Codigo INT,
    Valor VARCHAR(100)
)

INSERT INTO #Teste2
VALUES(1, '255.55'), (1, '0'), (2, '1'), (3, '1.99'), (4, NULL)

Devolvamos el valor más grande de la tabla agrupado por código:

SELECT Codigo, MAX(Valor) 
FROM #Teste2
GROUP BY Codigo

Resultado:

Hmm... El código 4 devolvió NULL. Quiero reemplazar este valor con 0 (cero).

SELECT Codigo, MAX(ISNULL(Valor,  0)) 
FROM #Teste2
GROUP BY Codigo

Resultado:

Funcionó como se esperaba. Pero prefiero usar la función COALESCE, así que cambiaré el código SQL para usarla:

SELECT Codigo, MAX(COALESCE(Valor,  0)) 
FROM #Teste2
GROUP BY Codigo

Resultado:

Mensaje de error:

¿Qué pudo haber pasado? Con la función ISNULL funcionó, pero con la función COALESCE dio error!

Esto sucede porque, como expliqué anteriormente, el retorno de la función ISNULL considera el tipo de datos del primer parámetro, que es la columna Valor (VARCHAR). Entonces el retorno de la función es el valor '0' (cero convertido a cadena).

En la función COALESCE, el tipo de datos devuelto es el mismo que el primer valor no nulo, que sería el valor entero 0 (cero). Y con eso, SQL generará un mensaje de error al intentar convertir el valor '255.55' (como una cadena) al tipo de datos entero (int):

Ejemplo práctico:

Si aún desea utilizar la función COALESCE, tendrá que convertir los parámetros de entrada a un tipo común para que SQL Server pueda convertir todos los parámetros.

SELECT 
    Codigo, 
    MAX(
        COALESCE(
            CAST(Valor AS NUMERIC(18, 2)
        ),  0)
    ) 
FROM
    #Teste2
GROUP BY
    Codigo

Resultado:

Diferencia #3 – Nulidad del retorno de función

Otra diferencia a la que la gente no le presta atención es la invalidez del retorno de la función. El nulo devuelto por la función ISNULL es siempre del tipo no nulo (NOT NULL) (asumiendo que el valor devuelto no es nulo). Por otro lado, la función COALESCE siempre devuelve datos con el tipo nulo (NULL), incluso si la función devuelve un valor no nulo.

Para que sea más fácil de demostrar, te prepararé dos ejemplos para que lo visualices mejor:

Ejemplo 1
Crearé una tabla y usaré columnas calculadas con las funciones ISNULL y COALESCE.

IF (OBJECT_ID('tempdb..#Teste2') IS NOT NULL) DROP TABLE #Teste2
CREATE TABLE #Teste2 (
    Valor VARCHAR(100) NOT NULL,
    ValorNaoNulo1 AS (ISNULL(Valor, 0)),
    ValorNaoNulo2 AS (COALESCE(Valor, 0)),
)

Veamos el tipo de datos y las opciones de nulidad para estas columnas:

SELECT
    A.[name],
    A.column_id,
    C.[name],
    A.max_length,
    A.[precision],
    A.scale,
    A.is_nullable
FROM
    tempdb.sys.columns     A
    JOIN tempdb.sys.tables B ON A.[object_id] = B.[object_id]
    JOIN tempdb.sys.types  C ON A.system_type_id = C.user_type_id
WHERE
    B.[name] LIKE '#Teste2%';

Resultado:

Como podemos ver arriba, el tipo de datos de la función ISNULL siguió el mismo tipo que el primer parámetro (Valor de columna = VARCHAR(100)), mientras que la función COALESCE usó como tipo de retorno el tipo del primer parámetro que se garantiza que no será nulo, que era el valor 0 (cero), un número entero.

Además, la columna calculada creada usando la función ISNULL se creó con el nulo determinado como NOT NULL, mientras que la columna creada usando la función COALESCE se creó con el nulo determinado como NULL, aunque siempre devuelve un valor no nulo porque el último parámetro es un valor fijo 0.

Es decir, aunque la función COALESCE siempre devuelve un valor no nulo en este ejemplo, la nulidad de la columna se definió como la aceptación de valores nulos.

Ejemplo 2
Este ejemplo ya puede causarle dolor de cabeza si intenta crear una clave principal utilizando columnas calculadas y la función COALESCE.

El siguiente comando funciona normalmente, creando una clave primaria en la columna “ValorNaoNulo1”, siendo una columna calculada usando la función ISNULL:

IF (OBJECT_ID('tempdb..#Teste3') IS NOT NULL) DROP TABLE #Teste3
CREATE TABLE #Teste3 (
    Valor VARCHAR(100) NOT NULL,
    ValorNaoNulo1 AS (ISNULL(Valor, 0)) PRIMARY KEY,
    ValorNaoNulo2 AS (COALESCE(Valor, 0)),
)

Resultado:

Sin embargo, si intentamos crear una clave principal en la columna ValorNaoNulo2, que es una columna calculada usando la función COALESCE, veremos un mensaje de error:

IF (OBJECT_ID('tempdb..#Teste3') IS NOT NULL) DROP TABLE #Teste3
CREATE TABLE #Teste3 (
    Valor VARCHAR(100) NOT NULL,
    ValorNaoNulo1 AS (ISNULL(Valor, 0)),
    ValorNaoNulo2 AS (COALESCE(Valor, 0)) PRIMARY KEY,
)

Resultado:

Mensaje de error:

Como deja claro el mensaje de error, esto sucedió porque la columna calculada se creó permitiendo valores nulos (debido al uso de la función COALESCE) y para crear una clave principal, la columna debe tener la capacidad de nulidad configurada para NO permitir valores nulos (NO NULOS).

¡Así que eso es todo, amigos!
Espero que te haya gustado este tip y un fuerte abrazo!

Referencias:

– https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql
– https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql