IsNULL and Coalesce proper usage

问题:

As we have two options to intercept the null values coming from database...

1. ISNull
2. Coalesce

Following are the ways to write the query for the above two functions...

Select IsNull(Columnname, '') As validColumnValue From TableName

Select Coleasce(Columnname, '') As validColumnValue From TableName

Query - Which should be prefered in which situation and why?

回答1:

This has been hashed and re-hashed. In addition to the tip I pointed out in the comment and the links and explanation @xQbert posted above, by request here is an explanation of COALESCE vs. ISNULL using a subquery. Let's consider these two queries, which in terms of results are identical:

SELECT COALESCE((SELECT TOP (1) name FROM sys.objects), N'foo');

SELECT ISNULL((SELECT TOP (1) name FROM sys.objects), N'foo');

(Comments about using TOP without ORDER BY to /dev/null/ thanks.)

In the COALESCE case, the logic actually gets expanded to something like this:

SELECT CASE WHEN (SELECT TOP (1) ...) IS NULL
    THEN (SELECT TOP (1) ...)
    ELSE N'foo'
END

With ISNULL, this does not happen. There is an internal optimization that seems to ensure that the subquery is only evaluated once. I don't know if anyone outside of Microsoft is privy to exactly how this optimization works, but you can this if you compare the plans. Here is the plan for the COALESCE version:

And here is the plan for the ISNULL version - notice how much simpler it is (and that the scan only happens once):

In the COALESCE case the scan happens twice. Meaning the subquery is evaluated twice, even if it doesn't yield any results. If you add a WHERE clause such that the subquery yields 0 rows, you'll see similar disparity - the plan shapes might change, but you'll still see a double seek+lookup or scan for the COALESCE case. Here is a slight different example:

SELECT COALESCE((SELECT TOP (1) name FROM sys.objects 
    WHERE name = N'no way this exists'), N'foo');

SELECT ISNULL((SELECT TOP (1) name FROM sys.objects 
    WHERE name = N'no way this exists'), N'foo');

The plan for the COALESCE version this time - again you can see the whole branch that represents the subquery repeated verbatim:

And again a much simpler plan, doing roughly half the work, using ISNULL:

You can also see this question over on dba.se for some more discussion:

https://dba.stackexchange.com/questions/4274/performance-difference-for-coalesce-versus-isnull

My suggestion is this (and you can see my reasons why in the tip and the above question): trust but verify. I always use COALESCE (because it is ANSI standard, supports more than two arguments, and doesn't do quite as wonky things with data type precedence) unless I know I am using a subquery as one of the expressions (which I don't recall ever doing outside of theoretical work like this) or I am experiencing a real performance issue and just want to compare to see if COALESCE vs. ISNULL has any substantial performance difference (which outside of the subquery case, I have yet to find). Since I am almost always using COALESCE with arguments of like data types, I rarely have to do any testing other than looking back at what I've said about it in the past (I was also the author of the aspfaq article that xQbert pointed out, 7 years ago).

回答2:

begin humor: The 1st, the 2nd will never work it's spelled wrong :D END humor

---Cleaned up response---

Features of isNull(value1,value2)

• Only supports 1 valuation, if the first is null, the 2nd will be used, so if its null too you get null back!
• is non-ANSI standard. Meaning if database portability is an issue, don't use this one
• isnull(value1,value2) will return the datatype for Value1
• will return the datatype for the selected value and fail when implicit conversions can't occur

Features of Coalesce(Value1, Value2, value3, value...)

• Supports multiple valuations of Null; basically will pull in the first non-null value from the list. if all values are null in list, null is returned.
• is ANSI-standard meaning database portability shouldn't be an issue.
• will return the datatype for the selected value and fail if all fields in the select do not return the same datatype.

So to answer the question directly: It depends on the situation if you need to develop SQL that

• is DB independent; coalesce is more correct to use.
• allows for multiple evaluations; coalesce is more correct (of course you could just embed isnull over and over and over...) but put that under a performance microscope, and coalesce may just win. (i've not tested it)
• are you using a db engine that supports isNull? (if not use coalesce)
• how do you want type casting handled? implicitly or not.

---ORIGINAL------ is null only supports 2 evaluations

coalesce supports many more... coalesce (columnName1, ColumnName2, ColumnName3, '')

coalesce returns datatype similar to that of case evaluation, whereas isnull returns datatype of first in list. (which I found interesting!)

As to when to use which. you'd have to investigate by looking at execution plan of both on SQL 2008 and 2005, different versions different engines different ways to execute.

Furthermore coalesce is ansii standard, isnull is engine specific. Thus if you want greater portability between dbengines use coalesce.

More info here aspfaq or here msdn blog

回答3:

you may take this onto consideration.

1. ISNULL function required two parameters: the value to check and the replacement for null values

   2.COALESCE function works a bit different COALESCE will take any number of parameters and return the first non-NULL value , I prefer COALESCE over ISNULL 'cause meets ANSI standarts, while ISNULL does not.

   I hope you found the answer to your question.