Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Three differences between COALESCE and ISNULL

From Wiki

Jump to: navigation, search

There are three major differences besides being ANSI or not between COALESCE and ISNULL 1) COALESCE correctly promotes its arguments to the highest data type in the expression list, ISNULL does not 2) ISNULL can only work with 2 values while COALESCE can take a lot more 3) The alternate value takes the length of the first vale with ISNULL, with COALESCE this doesn't happen


Let's get started, run the following blocks of code

The result is 7, integer math

  1. SELECT 15 / ISNULL(CONVERT(INT,NULL), 2.00)


The result is 7.5, which is correct

  1. SELECT 15 / COALESCE(CONVERT(INT,NULL), 2.00)


You will see that the result is not the same ISNULL does integer math while COALESCE does not

COALESCE correctly promotes its arguments to the highest data type in the expression list. ISNULL just looks at the first datatype, which is an integer (15) and makes everything an int COALESCE looks at 2.00 and 15 and then promotes the integer to decimal

Another example is returning the first non null value, ISNULL can only take 2 values while COALESCE can take a whole lot more Here we have 4 variables and all except for one are null

  1. DECLARE @Var1 VARCHAR(20)
  2.     DECLARE @Var2 VARCHAR(20)
  3.     DECLARE @Var3 VARCHAR(20)
  4.     DECLARE @Var4 VARCHAR(20)
  5.      
  6.     SELECT @Var4 = 'ABC'
  7.      
  8.     --This will return ABC
  9.     SELECT COALESCE(@Var1,@Var2,@Var3,@Var4)


Last example. ISNULL returns NOT while COALESCE returns Not There.

  1. DECLARE @v VARCHAR(3)
  2.     SELECT  COALESCE(@v,'Not There')
  3.     SELECT  ISNULL(@v,'Not There')


The alternate value takes the length of the first vale with ISNULL, with COALESCE this doesn't happen


Contributed by: --SQLDenis 03:15, 31 May 2008 (GMT)

Part of SQL Server Programming Hacks

Section Pitfalls

189 Rating: 2.4/5 (91 votes cast)