Three differences between COALESCE and ISNULL
From Wiki
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
- SELECT 15 / ISNULL(CONVERT(INT,NULL), 2.00)
The result is 7.5, which is correct
- 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
- DECLARE @Var1 VARCHAR(20)
- DECLARE @Var2 VARCHAR(20)
- DECLARE @Var3 VARCHAR(20)
- DECLARE @Var4 VARCHAR(20)
- SELECT @Var4 = 'ABC'
- --This will return ABC
- SELECT COALESCE(@Var1,@Var2,@Var3,@Var4)
Last example. ISNULL returns NOT while COALESCE returns Not There.
- DECLARE @v VARCHAR(3)
- SELECT COALESCE(@v,'Not There')
- 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
