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
Improve this question
7
  • What are the actual correct semantics if all of the columns are null? Should the result be 0 or NULL?
    – Martin Smith
    Aug 18, 2016 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, 2016 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, 2016 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, 2016 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, 2016 at 16:28

1 Answer 1

Reset to default
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.

Improve this answer
4
  • 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, 2016 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, 2016 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, 2016 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, 2016 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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