coalesce compared to isnull 1

[sds814]

In the where clause, is it correct to do

where COALESCE(A.ProductRequired, 'N') = 'Y'

or

where ISNULL(A.ProductRequired, 'N') = 'Y'

The BOL says that COALESCE "returns the first nonnull expression among its arguments." But I'm not exactly sure what that means.

Thanks.

 

[jbenson001]

The BOL says that COALESCE "returns the first nonnull expression among its arguments." But I'm not exactly sure what that means.

Look at the example in BOL,it shows you what it means. In your case, either statment will work.

 

[memdiscount]

It means if you pass 2 or more than 2 arguments (which is only possible with COALESCE ) then it will return you first non-null value from passed arguments:

********************************
For Example:

Select COALESCE ('ABC', Null, 'XYZ')

it will return you 'ABC' which is the first non-null value out of three.

********************************

Select COALESCE (Null, Null, 'XYZ')

It returns 'XYZ'

********************************

So, in your case,

where COALESCE(A.ProductRequired, 'N') = 'Y'

If A.ProductRequired will be Null then it will return 'N' and your check will work.

However, Isnull is more appropriate in this case as you have only two arguments.

There are several use of COALESCE function, to see those use read this article.

http://www.mssqltips.com/tip.asp?tip=1521

Thanks.

 

[bborissov]

Check this:

declare @test1 int
declare @test2 numeric(14,2)
SET @Test2 = 12
SELECT ISNULL(@test1,@test2), COALESCE(@Test1,@test2)

As you see ISNULL() returns integer type but COALESCE() returns numeric(14,2).
COALESCE always return higher type of all parameters.

That is not the case in your example but you must have this in mind

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[r937]

COALESCE and ISNULL are the same if used with 2 parameters, and only COALESCE allows more than 2

one other important difference is that COALESCE is Standard SQL, meaning that if you ever need to port your application to another database system, you won't have to change your queries

r937.com | rudy.ca