SQL Server - ISNULL x COALESCE: Conhecendo as diferenças entre as duas funções

Um erro muito comum entre desenvolvedores SQL é sobre as diferenças entre as funções ISNULL e COALESCE, onde a grande maioria das pessoas acredita que seja apenas no fato da função ISNULL permitir apenas 1 parâmetro de entrada para fazer a análise de valores nulos, enquanto a função COALESCE permite que você passe N parâmetros de entrada.

Entretanto, existe outra diferença, bem importante, que muitas pessoas acabam não prestando atenção e que pode te fazer gastar um bom tempo tentando debuggar um problema e é isso que explicarei nesse artigo.

Tive um processo de ETL que deu erro por causa dessa diferença de tipos de dados entre ISNULL e COALESCE e acabei criando este artigo por entender que outras pessoas podem acabar perdendo tempo tentando entender o que aconteceu.

Diferença #1 – Quantidade de parâmetros da função

A primeira grande diferença e a mais conhecida entre as funções ISNULL e COALESCE é a quantidade de parâmetros que as funções aceitam.

Criação da base de testes

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')

Exemplo:

Retornar o primeiro valor não-nulo de cada linha ou 0, se todas as colunas forem 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 observar no código acima, a função ISNULL aceita apenas um parâmetro de entrada, que pode ser uma coluna ou um valor, e um parâmetro para você definir o valor de substituição caso o primeiro parâmetro seja nulo. Caso você queira fazer comparações entre várias colunas/valores, terá que utilizar a função várias vezes, aninhadamente.

Já a função COALESCE, aceita vários parâmetros de entrada (no mínimo 2 parâmetros e sem máximo definido), tornando o uso dessa função mais simples do que a ISNULL.

Diferença #2 – Tipo de dado do retorno

Uma diferença que muitas pessoas acabam não percebendo é com relação ao tipo de dados do retorno: Enquanto a função ISNULL considera o tipo de dados da primeira coluna como o tipo de dados do retorno da função, o tipo de dados do retorno da função COALESCE será o mesmo tipo de dados do parâmetro que será retornado (o primeiro não-nulo).

Na prática, esse cenário faz com que as duas funções tenham um comportamento muito diferente.

Criação da base de testes

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)

Vamos retornar o maior valor da tabela agrupado por código:

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

Resultado:

Humm.. O código 4 retornou NULL. Quero substituir esse valor por 0 (zero).

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

Resultado:

Funcionou conforme o esperado. Mas prefiro usar a função COALESCE, então alterarei o código SQL para usá-la:

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

Resultado:

Mensagem de erro:

O que será que aconteceu? Com a função ISNULL funcionou, mas com a função COALESCE deu erro!

Isso acontece porque, como eu havia explicado acima, o retorno da função ISNULL considera o tipo de dados do primeiro parâmetro, que é a coluna Valor (VARCHAR). Então o retorno da função, é o valor ‘0’ (zero convertido para string).

Já na função COALESCE, o tipo de dados de retorno é o mesmo do primeiro valor não-nulo, que seria o valor inteiro 0 (zero). E com isso, o SQL irá gerar uma mensagem de erro ao tentar converter o valor ‘255.55’ (como string) para o tipo de dados inteiro (int):

Exemplo prático:

Caso você ainda queira utilizar a função COALESCE, você terá que converter os parâmetros de entrada para um tipo em comum que todos os parâmetros possam ser convertidos pelo SQL Server.

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

Resultado:

Diferença #3 – Nulidade do retorno da função

Outra diferença que as pessoas não se atentam, é em relação à nulidade do retorno da função. A nulidade retornada pela função ISNULL é sempre do tipo não-nulo (NOT NULL) (supondo que o valor retornado não seja nulo). Por outro lado, a função COALESCE sempre retorna os dados com o tipo nulo (NULL), mesmo que a função retorne um valor não-nulo.

Para ficar mais fácil de demonstrar, vou preparar dois exemplos para você visualizar isso melhor:

Exemplo 1
Criarei uma tabela e utilizarei colunas calculadas com as funções ISNULL e 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)),
)

Vamos observar o tipo de dados e opções de nulidade dessas colunas:

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 observar acima, o tipo de dados da função ISNULL seguiu o mesmo tipo do primeiro parâmetro (Coluna Valor = VARCHAR(100)), enquanto a função COALESCE utilizou como tipo de retorno, o tipo do primeiro parâmetro que garantidamente não será nulo, que foi o valor 0 (zero), um inteiro.

Além disso, a coluna calculada criada utilizando a função ISNULL, foi criada com a nulidade determinada como não nula (NOT NULL), enquanto a coluna criada utilizando a função COALESCE foi criada com a nulidade nula (NULL), mesmo que ela sempre retorne um valor não-nulo por causa do último parâmetro ser um valor fixo 0.

Ou seja, mesmo que a função COALESCE sempre retorne um valor não-nulo nesse exemplo, a nulidade da coluna foi definida como aceitando valores nulos.

Exemplo 2
Esse exemplo já pode dar uma dor de cabeça se você tentar criar uma primary key utilizando colunas calculadas e a função COALESCE.

O comando abaixo funciona normalmente, criando uma primary key na coluna “ValorNaoNulo1”, sendo uma coluna calculada utilizando a função 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:

Entretanto, tentar criar uma primary key na coluna ValorNaoNulo2, que é uma coluna calculada utilizando a função COALESCE, vamos ver uma mensagem de erro:

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:

Mensagem de erro:

Como a mensagem de erro nos deixa bem claro, isso aconteceu porque a coluna calculada foi criada permitindo valores nulos (por causa do uso da função COALESCE) e para criar uma primary key, a coluna deve ter a nulidade definida como NÃO permitindo valores nulos (NOT NULL).

Então é isso, pessoal!
Espero que tenham gostado dessa dica aí e um grande abraço!

Referências:

– 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