Why does Sql Sum of multiple columns containing nulls return incorrect result?

Question

Table containing the following values:

Column1  Column2
1        NULL
NULL     4
2        NULL
NULL     5
3        6

The following query:

SELECT 
    SUM([Column1] + [Column2] ) 
FROM [myTable]

returns a value of 9 when it should be returning 21. Why? How does it arrive at the value?

I know the SUM can be corrected by adding ISNULL like so:

SELECT 
    SUM(ISNULL([Column1], 0) + ISNULL([Column2], 0)) 
FROM [myTable]

but I would like to know the logic behind the value 9

Answer 1

use COALESCE to convert null into 0. (that's it if you want null values as zero.)

SELECT SUM(COALESCE(column1,0) + COALESCE(column2,0))
FROM table1

• See SQLFIDDLE Demo