2

I have inherited a table that has NULL values for float fields, instead of a 0 value. I am doing some basic testing for querying, and in my tests both ISNULL() and COALESCE() present the same accurate output. In my testing (a table with roughly 5K records) time difference is very negligible as well.

Question being with the sample DDL below, is there a benefit to use ISNULL() over COALESCE() or vice-versa? Are there circumstances that I have yet to see that could provide an inaccurate calculation?

Create Table #Test
( 
  blue float
  ,red float
  ,green float
)

Insert Into #Test VALUES 
('14', NULL, '12')
,(NULL, '12', '10')
,(NULL, NULL, '8')
,('10', '2', NULL)

Select 
ISNULL(blue,0)+ISNULL(red,0)+ISNULL(green,0)
FROM #Test

SELECT 
COALESCE(blue,0)+COALESCE(red,0)+COALESCE(green,0)
FROM #Test
  • What are the actual correct semantics if all of the columns are null? Should the result be 0 or NULL? – Martin Smith Aug 18 '16 at 15:33
  • @MartinSmith - if all the columns are null, then I would want a 0 returned. Does that answer your question? – user2676140 Aug 18 '16 at 15:34
  • Yes. I wasn't sure if you were just trying to effectively ignore null (like sum does) or actively replace them with zero. – Martin Smith Aug 18 '16 at 15:36
  • 1
    In this simple scenario, there's not much difference. Read up on ISNULL vs COALESCE and how they differ in the data TYPE that they return. That can be significant sometimes. – R Evans Aug 18 '16 at 15:36
  • 1
    For me this is a simple choice: COALESCE is Standard SQL supported by almost every DBMS (besides Access) while ISNULL is MS proprietary syntax – dnoeth Aug 18 '16 at 16:28
1

COALESCE can take a whole list of values to check for NULL where ISNULL takes one check value and would require nesting to check more values.

SELECT 
COALESCE(col1, col2, col3, col4, col5, 0) AS value_returned
FROM myTable

vs

SELECT 
ISNULL(col1, ISNULL(col2, ISNULL(col3, ISNULL(col4, 0)))) AS value_returned 
FROM myTable

Since your case is using float values, there should not be a difference in results from calculations. The only benefit I can really see if code readability and minimizing the amount of nesting needed.

  • As mentioned in the comments of the OP, you must take into consideration mixing data types. If your column checks are varchar and you want to return an int, you need to cast. – dfundako Aug 18 '16 at 15:37
  • 1
    I understand that COALESCE() will return the first non-null field back, but how would that help in a calculation sense? From my testing if a null value is reached in a calculation then a 0 is immediately returned. Forgive me if I am missing the obvious here. – user2676140 Aug 18 '16 at 15:39
  • 1
    Both are fine for your calculations and should not return a different result when both values are considered float. Since that appears to be the case, the next consideration (in my opinion) is readability and code minimization. Coalesce is cleaner. – dfundako Aug 18 '16 at 15:41
  • Yes, all fields are float data type in this table. Thank you for the recommend on using COALESCE() for readability & minimization. I will go that route since no other factors seem to play a part. – user2676140 Aug 18 '16 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.