By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

SQL NULL value logic

Expert 100+
P: 427

I was playing around with SQL (SQL Server 2008) toady and noticed something I think is really strange:

Expand|Select|Wrap|Line Numbers
  1. select 1/null -- returns null
Why is this operation allowed? Why is any mathematical operation allowed on NULL?

NULL as I know it in programming is an undefined value. In math, when dividing by 0 you get an undefined value.
Thus NULL = x/0 ?

SQL does throw divide by 0 exceptions, so why are division by NULL or comparison to NULL exceptions not raised?

PS: Please excuse me if this is a silly question, but I generally don't program anything other than the standard CRUD statements in SQL.
Jan 13 '10 #1
Share this Question
Share on Google+
2 Replies

Expert 2.5K+
P: 2,878
I think this is because any operation against a NULL is NULL. The reason that it is allowed is because the value is "unknown". Since even the server does not know what's inside NULL, it allow the operation and just return an unknown value (NULL)...

Just my two cents...

~~ CK
Jan 13 '10 #2

Expert 100+
P: 427
I understand it returning NULL, but that is the essence of my problem with this scenario. You can never retrieve any meaningful value from an operation using a NULL. So why even allow it?
Most programming languages throw exceptions for exactly this reason, if feel SQL should too.

Does that mean that every single variable must be checked each time that it is used (calling the isnull() function) and manually raising an exception if the value is NULL? The whole scenario just feels awkward and poorly implemented IMHO.
Jan 14 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.