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

Exemplo:

Retornar o primeiro valor não-nulo de cada linha ou 0, se todas as colunas forem NULL

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

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

Resultado:

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

Resultado:

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

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.

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.

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

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:

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:

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