NULLS not behaving as expected

Question

NULLS not behaving as expected

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Ian Lampert SSC Enthusiast Points: 175 More actions · Answer 1

I'm repeating myself now...NULLs have no determinable value.

The boolean predicate is NOT behaving as expected! I expected it to return TRUE or FALSE based on the argument...it does not do that. A NULL is not a '0' and, I think quite reasonably, I expected the predicate to return TRUE because NULL <> '0'. No-one has been able to put up a reasonable argument otherwise.

The issue here is not what a NULL means (I think we are going off track here) but what should be returned by a boolean argument. If I argued:

WHERE field1 = 'frustrated sql developers'

I would not expect it to return rows with NULLs in field1 since it is not TRUE. Since a boolean has only two possible values (for years the way I have been taught) then it follows that it is reasonable to expect:

WHERE field1 <> 'frustrated sql developers'

to return TRUE where there is a NULL in field1. Unless, of course, someone is going to convince me that a NULL = 'frustrated sql developers'. However, I am beginning to believe it.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

The boolean predicate is NOT behaving as expected! I expected it to return TRUE or FALSE based on the argument...it does not do that. A NULL is not a '0' and, I think quite reasonably, I expected the predicate to return TRUE because NULL <> '0'. No-one has been able to put up a reasonable argument otherwise.

The fact that it is not returning what YOU expect, doesn't mean it isn't working as expected. Check this out http://www.microsoft.com/technet/prodtechnol/sql/70/books/relation.mspx#EHAA or google on this. If you find this reasonable or not, is kind of irrelevant, because it is very unlikely to change a thing regarding the treatment of NULLs.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ian Lampert SSC Enthusiast Points: 175 More actions · Answer 3

"Most of the operations of relational algebra involve the use of logical operators, operators that usually return a Boolean result—that is, True or False." - quote from your article.

That is why it is reasonable.

SQL may be acting as you expected but not as I expected (not sure why you have a problem with that) as I was taught the above. Maybe you were taught differently or maybe you're just reciting articles.

I now know what I need to do to deal with NULLs. Thanx for your help.

ron k SSCertifiable Points: 6103 More actions · Answer 4

And to complete the quote from the article:

"...I say "usually" because with the addition of nulls to the relational model things get a little more complicated.

Nulls add a third value to the set of Boolean values; you must then work with True, False, and Null. Not surprisingly, these operators become known as three-valued logic. The three-valued truth tables for the standard logical operators are shown in Figure 5-1."