SQL Server – ISNULL vs COALESCE differences

Microsoft Learn documentation: COALESCE

Microsoft Learn documentation: ISNULL

Intro & basics

ISNULL and COALESCE are two important functions in Microsoft SQL Server that allow you to handle null values in your database queries. Although both functions serve a similar purpose, there are some differences between them that you should be aware of.

The ISNULL function replaces a null value with a specified value. It takes two arguments: the first argument is the value to be checked for null, and the second argument is the value to be returned if the first argument is null. Here’s an example:

SELECT ISNULL(NULL, 'Unknown');

In this example, the ISNULL function checks if the first argument is null. Since it is null, the function returns the second argument, which is 'Unknown’. Therefore, the result of this query is 'Unknown’.

The COALESCE function also replaces a null value with a specified value. However, it takes multiple arguments and returns the first non-null value. In this example, the COALESCE function checks the first argument for null. Since it is null, it moves on to the next argument, which is 'Unknown’. This value is not null, so the function returns 'Unknown’ and does not evaluate any further arguments. Therefore, the result of this query is 'Unknown’.

SELECT COALESCE(NULL, 'Unknown', 'Not applicable');

Similarities

DECLARE @price int                 -- declare integer variable with NULL value

SELECT COALESCE(@price, 'Unknown') -- COALESCE fails
SELECT ISNULL(@price, 'Unknown')   -- ISNULL fails

 

 

DECLARE @price int = 10                 --declare integer variable with value = 10

SELECT COALESCE(@price, 'Unknown') -- COALESCE successful
SELECT ISNULL(@price, 'Unknown')   -- ISNULL successful

Why ISNULL() truncates the string and COALESCE is not?

A tricky difference happens, when we use a field (variable or entire column of a table) and we play around ISNULL and COALESCE. Some of you may find on of these functions working in an unexpected way. But, in reality, it’s expected and understandable – all explained on one screen below.

DECLARE @title nvarchar(4)           -- declare variable with null value

SELECT ISNULL(@price, 'Unknown')     -- ISNULL inherits the type of the first argument
SELECT COALESCE(@price, 'Unknown')   -- COALESCE returns the second argument type & value

 

A nice explanation is provided by Microsoft in ISNULL documentation.

 

Differences:

    • ISNULL takes only two arguments, while COALESCE takes multiple arguments
    • ISNULL always returns the second argument if the first argument is null, whereas COALESCE returns the first non-null value among its arguments.
    • ISNULL uses the data type of the first parameter, COALESCE returns the type of value based on the order of arguments
    • ISNULL is not an ANSI-standard function, while COALESCE is alligned with it
Back to main