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)

FROM #Test

FROM #Test
  • What are the actual correct semantics if all of the columns are null? Should the result be 0 or NULL? Commented 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? Commented 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. Commented 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
    Commented 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
    Commented Aug 18, 2016 at 16:28

1 Answer 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.

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


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
    Commented 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. Commented 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
    Commented 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. Commented Aug 18, 2016 at 15:43

Your Answer

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

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