How to handle NULL values in SQL Server: ISNULL and COALESCE

João Barros 14 de July de 2026 4 min read

In a real database it is normal to find columns with no value: missing phone numbers, unfilled commissions or dates that are still unknown. Those empty spaces are NULL values and, if left untreated, they produce wrong totals and reports full of blank cells. In SQL Server, the ISNULL and COALESCE functions exist precisely for this: they return an alternative value whenever they find a NULL.

Prerequisites

Access to SQL Server (2012 or later) or Azure SQL Database.
SQL Server Management Studio or Azure Data Studio to run the queries.
A test database and basic knowledge of SELECT in T-SQL.

Step 1: Understand what a NULL value is

NULL is not zero, nor an empty string: it represents the absence of information, something unknown. Because of that, it cannot be compared with the equals operator. To test whether a column is empty you always use IS NULL or IS NOT NULL.

-- Errado: NULL nao e igual a nada, nunca devolve linhas
SELECT nome FROM Clientes WHERE telefone = NULL;

-- Correto
SELECT nome FROM Clientes WHERE telefone IS NULL;

Step 2: Replace NULL with ISNULL

The ISNULL function takes two arguments: the expression to check and the value to return if it is NULL. It is the most direct way to show friendly text instead of an empty space.

SELECT
    nome,
    ISNULL(telefone, 'Sem contacto') AS telefone
FROM Clientes;

If the telefone column has a value, it is kept; if it is NULL, the fallback text ("Sem contacto") appears. The same idea works for numbers, which is essential in calculations:

SELECT
    produto,
    preco * ISNULL(quantidade, 0) AS total
FROM Vendas;

Without ISNULL, any row with a NULL quantidade would give a NULL total and contaminate the whole operation.

Step 3: Use COALESCE for multiple alternatives

COALESCE goes further: it accepts several arguments and returns the first one that is not NULL. It is ideal when there is a cascade of columns and you want the first one that is filled in.

SELECT
    nome,
    COALESCE(telemovel, telefone_fixo, email, 'Sem contacto') AS contacto
FROM Clientes;

SQL Server evaluates from left to right: if telemovel exists it uses it; otherwise it moves on to telefone_fixo, then email and, finally, the default text. A single function solves what would require several nested CASE expressions.

Step 4: Know when to use each one

The two functions look the same, but they have important differences, described in the official Microsoft documentation:

Number of arguments: ISNULL accepts only two; COALESCE accepts several.
Portability: COALESCE is part of the ANSI SQL standard and exists in other databases; ISNULL is exclusive to SQL Server.
Data type: ISNULL takes the type of the first argument and may truncate the replacement value; COALESCE follows the data type precedence rules.

That type difference is a frequent trap. Look at the example:

DECLARE @codigo varchar(2) = NULL;

SELECT ISNULL(@codigo, 'ABCDE')   AS com_isnull,   -- devolve 'AB'
       COALESCE(@codigo, 'ABCDE') AS com_coalesce; -- devolve 'ABCDE'

Because @codigo is varchar(2), ISNULL cuts the text to two characters, while COALESCE keeps the full value. So a good rule is to use COALESCE by default and reserve ISNULL for simple two-option cases.

Tip: in numeric columns used in sums or averages, handle NULL with 0 (or the right value for your calculation) before aggregating.

Check the result

To confirm that the treatment worked, count how many rows still have no contact after applying COALESCE. If everything is correct, the result should be 0:

SELECT COUNT(*) AS ainda_sem_contacto
FROM (
    SELECT COALESCE(telemovel, telefone_fixo, email) AS contacto
    FROM Clientes
) AS t
WHERE contacto IS NULL;

A value of 0 means every row now has a contact. If a larger number appears, those rows have all three columns empty at the same time and need a final default value.

Conclusion

With ISNULL and COALESCE you get to control exactly what appears when data is missing, avoiding wrong sums and reports with blank spaces. The next step is to apply these functions inside views or measures, so the data arrives already clean in tools like Power BI. Which column in your model most needs a default value?