SQL ISNULL and COALESCE Functions – What Are The Differences And Usage

Last modified date:

ISNULL and COALESCE are functions used for work with null values (NULL). Both functions return first value out of previously defined entry parameters. There are also some differences between both functions.

ISNULL Syntax:

SELECT ISNULL(<Column>, <Column Replacement - other column or logic>)
FROM dbo.Table 

COALESCE Syntax:

SELECT COALESCE(<Column>, <Column Replacement - other column or logic 1>, <Column Replacement - other column or logic 2>,...)
FROM dbo.Table

ISNULL and COALESCE Differences

  • ISNULL accepts only two parameters, COALESCE two or more parameters
  • ISNULL returns result in data type of entry parameters. If all inserted parameters are NULL, result is NULL
  • Data type of COALESCE is evaluated dynamically and it is chosen according to data priority – data precedence. If all entries are NULL, result is error. More on Data precedence in article by Dave Pinal
  • ISNULL is faster from performance point of view

ISNULL and COALESCE Examples

We will declare two variables @X and @Y with data type VARCHAR(1) and VARCHAR(50) and then compare different combinations.

DECLARE @X VARCHAR(1) = NULL
DECLARE @Y VARCHAR(50) = 'Functions COALESCE and ISNULL are great'

SELECT
COALESCE(@X, @Y) AS COALESCE_XY,
ISNULL(@X, @Y) AS ISNULL_XY,
COALESCE(@Y, @X) AS COALESCE_YX,
ISNULL(@Y, @X) AS ISNULL_YX

Isnull vs coalesce priklad

Conclusion:

  1. COALESCE (X,Y) returns full text in VARCHAR(50) data type – it has priority from data precedence point of view
  2. ISNULL (X,Y) returns the result in VARCHAR(1) according to first parameter
  3. COALESCE(Y,X) returns again VARCHAR(50)
  4. ISNULL (Y,X) returns VARCAHR(50) this time, longer text is inserted as first parameter

Special examples:

-- 1) Result je NULL
SELECT ISNULL(NULL, NULL)

-- 2a) Result is Error
SELECT COALESCE(NULL,NULL)

-- 2b) Result is NULL
DECLARE @X VARCHAR(1) = NULL
DECLARE @Y VARCHAR(50) = NULL

SELECT COALESCE(@X,@Y)

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Category: SQL Functions

About Ing. Jan Zedníček - BI Developer, Finance controller

My name is Jan Zedníček and I work as a BI Developer at Kentico Software in Brno. Mostly you can see me there working in the office, but I also work partly as a freelancer. I have been working as a freelancer for many companies for more than 5 years, but Kentico is the matter of my heart. I used to work as a financial controller at companies like Aero Vodochody or Sberbank and I also used to be a bond program manager in Unicapital Investment group. When I am not at work, I like playing volleyball, chess, doing a workout in the gym and I enjoy tasting of best quality rums. I am trying to summarize all my knowledge on this website not to forget them (because of the rum effect, you know =) and to put them forward to anyone. Don´t worry about asking for help or writing some comments.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.