`COALESCE`, `ISNULL` and `NULLIF`: Working with missing values

A NULL value represents unknown or unavailable information. It is not equivalent to zero, an empty string, or a string made only of spaces. Those states may need the same visible result, but they do not mean the same thing inside SQL Server.

COALESCE, ISNULL, and NULLIF can transform those values. The three functions are related, but each answers a different question.

Three different operations

`COALESCE`: choose the first available value

COALESCE examines its arguments from left to right and returns the first one that is not NULL. An argument is each value or expression supplied to a function.

If the first argument contains a value, later arguments no longer determine the result. If every argument is NULL, the result is also NULL.

COALESCE(Expresion1, Expresion2, Expresion3)

`ISNULL`: replace one value

ISNULL receives two arguments. If the first is NULL, it returns the second; if the first contains a value, it preserves that value.

This function performs a direct replacement. The result type and length also depend mainly on the first argument, so the alternative may be converted or truncated to fit it.

ISNULL(Expresion, ValorAlternativo)

`NULLIF`: turn equality into `NULL`

NULLIF compares two expressions. If they are equal, it returns NULL; if they differ, it returns the first expression.

It does not select an alternative. It transforms a value that should be treated as absent—such as an empty string or a zero used as a marker—before another operation receives it.

NULLIF(Expresion1, Expresion2)

Combining the functions

Several functions can be applied in stages. In this example, the mobile phone should appear first, while the home phone is used when the mobile phone contains no useful information.

TRIM removes spaces at the beginning and end of a string. An empty string contains no characters and is represented as ''.

The expression is evaluated from the inside out:

TRIM cleans each phone number.
NULLIF(texto, '') turns an empty result into NULL.
COALESCE chooses the first phone number that remains available.
If neither contains a value, it returns the text Sin teléfono.

Example: choose the first available phone

SELECT
    c.IdCliente,
    c.Nombre,
    COALESCE(
        NULLIF(TRIM(c.TelefonoMovil), ''),
        NULLIF(TRIM(c.TelefonoCasa), ''),
        'Sin teléfono'
    ) AS TelefonoContacto
FROM dbo.Cliente AS c
ORDER BY c.IdCliente;

Name	TelephoneContact
Ana Torres	7710001001
Bruno Diaz	7710001002
Carla Ruiz	No phone
Elena Mora	7710001005

Carla’s row remains because the query does not filter customers. Since both phone numbers become NULL, COALESCE reaches its last argument and returns Sin teléfono.

Substitute a single value

When there is only one alternative, ISNULL is straightforward:

SELECT
    c.Nombre,
    ISNULL(c.Correo, 'Sin correo') AS Correo
FROM dbo.Cliente AS c;

COALESCE does not take its result type from the first argument in the same way. SQL Server compares all expression types through its data type precedence rules: the type with the higher priority determines the conversions needed to produce one result. The difference matters when combining strings of different lengths or different numeric types.

Avoid division by zero

NULLIF can convert a divisor equal to zero to NULL:

DECLARE @Importe DECIMAL(12, 2) = 850.00;
DECLARE @Unidades INT = 0;

SELECT @Importe / NULLIF(@Unidades, 0) AS ImportePorUnidad;

DECLARE creates the local variables used by the example; its full syntax is explained in DECLARE, SET, and SELECT.

The result will be NULL instead of producing a division by zero error. If we need to display zero or another value, we can wrap the expression with COALESCE:

SELECT COALESCE(@Importe / NULLIF(@Unidades, 0), 0) AS ImportePorUnidad;

What should be checked

Replacing a null does not by itself correct the quality of the data. An empty string, a string with spaces, and a value NULL are different states; that’s why the example uses TRIM and NULLIF before COALESCE.

We must also check the resulting type. A seemingly innocent alternative may cause truncation or an implicit cast if its types are not compatible with the original expression.