Why Can't SQL Server Add NULL Values, Or Just Ignore Them in Math Operations? 1

[kjv1611]

Let's say I have 2 columns:

Col1[tab][tab]Col2
111.11[tab][tab]5131351.00
44.64[tab][tab]NULL
NULL[tab][tab]8431.21

I realize that I can use COALESCE to replace the NULL values with zero, but why can't SQL Server figure that out on it's own, so I can simply write:

SELECT Col1 + Col2

instead of

SELECT COALESCE(Col1,0) + COALESCE(Col2,0)

Any thoughts? Is it a logical problem that there is no choice in, something the programmers left out?

Thanks for any references, thoughts on how the SQL engine parses NULLS in mathematical operations.

As a side note, I can't seem to get my columns in this post to line-up. If I used [tt] for monospace, it made no difference - it's automatically removing multiple spaces which I was using to line up the columns. When I add tabs, it at least gives a little breathing room, but I can't get the columns to align properly.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SQLBill]

In SQL Server, NULL basically means "I don't know what this value is". It could be anything, zero, a space, no,..... that's why you have to tell SQL Server what NULL equates to by using ISNULL or COALESCE. Using + between two values doesn't always mean 'add', you could be combining the two columns into one string - then NULL might need to be a space. So SQL Server lets you determine what NULL is supposed to be based on how you are using it.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[kjv1611]

Gotcha - I didn't exactly think of it that way. I guess I was thinking more like - well, it could look at the other values in the equation, and determine what type it should be. Yeah - I use + for concatenation as well.

Thanks

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[gk53]

SQL server has isnull function so your query need to be just
SELECT isnull(Col1,0) + isnull(Col2,0) as total from yourTable

 

[jbenson001]

I believe that is what SQLBill already explained and what the poster explained they understand.