james (3/23/2009)
Yes, I understood what was going on (answered correctly) until I read the explanation. It should be mentioned somewhere in there that the NULL does not compare well to certain data types and therefore did not return a TRUE.
Actually, NULL does not compare to ANY data type.
NULL means no value is determined. For example, the winning percentage of a sports team may be defined as Wins/Matches. But before the season starts, the value of Matches is zero. Since you cannot divide by zero, you may be tempted to say the winning percentage is also zero, but truly there is no value as there have been no matches. A losing team that has zero wins over five matches has a real winning percentage of zero, but a team that has yet to play has no percentage at all. This may sound like pedantry and symantic parsing for no good reason, but it does allow for a more accurate representation of the real world in data form.
[p]
Since the value of NULL is not determined, it cannot be compared to anything, not even another NULL. Try this:
Declare @myInt1 int
Declare @myInt2 int
set @myInt1 = NULL
set @myInt2 = @myInt2
Declare @mychar1 char
Declare @mychar2 char
set @mychar1 = NULL
set @mychar2 = @mychar2
select @myInt1
,@myInt2
,@myChar1
,@myChar2
If @myInt1 = @myInt2
select 'Equal Int values'
else select 'Not Equal Int Values'
If @mychar1 = @mychar2
select 'Equal char values'
else select 'Not Equal char Values'
If @myInt1 = NULL
select 'Int1 = null'
else select 'Int1 Not Equal Null'
If @mychar1 = NULL
select 'Char1 Equal Null'
else select 'Char1 Not Equal Null'
[/p]