"Actually comparisons of a NULL (or a non-NULL value) to a NULL results in UNKNOWN. Your point is taken though: The WHERE clause only returns results for rows comparisons that evaluate to TRUE. Comparisons that evaluate to FALSE or UNKNOWN are dropped from the results."
Well now we're splitting hairs! But, Mike is absolutely right. BOL defines the result for those comparisons to be "UNKNOWN". I've always considered NULL and UNKNOWN to mean the same thing and, interestingly enough, when you look up "unknown data" in BOL you get the "Null Values" overview page! There must be some subtle distinction though, otherwise the authors would have used NULL instead of UNKNOWN.
Sorry to take this thread so far off topic, but here's an interesting puzzler (while we're splitting hairs). I apologize if this topic has already been covered elsewhere.
I refrained from bringing up the ANSI_NULLS setting before, but it adds an interesting twist. According to BOL:
When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN.
and
When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL. Also, a comparison of two expressions that have both evaluated to null values yields TRUE.
To confirm this, let's try some simple TSQL. Running the following produces what you'd expect (after reading BOL).
set ANSI_NULLS OFF
declare @string varchar(25)
set @string = 'TEST'
if @string <> NULL --compare a variable to explicit NULL
print 'true'
else
print 'not true'
set @string = NULL
if @string <> 'TEST' --compare a NULL variable to an explicit value
print 'true'
else
print 'not true'
SELECT 'true' where @string <> 'TEST' --try a comparison in a select.
Results
----
true
true
true
(1 row(s) affected)
set ANSI_NULLS ON and you get
Results
----
not true
not true
(0 row(s) affected)
So far so good. It looks like all we have to do to retrieve Bob in Ehsan's example is set ANSI_NULLS OFF, right?
set ANSI_NULLS OFF
SELECT * FROM Students s left outer join StudentExam se ON s.StID = se.StID WHERE se.ExamName<>'SQL Server'
Results
----
StID StName StID ExamName
2 Anna 2 VB.NET
2 Anna 2 C#.NET
1 Jack 1 XML
(3 row(s) affected)
Huh? Where is Bob? Hmmm. Let's continue to use ANSI_NULLS OFF and look for ExamName = NULL
set ANSI_NULLS OFF
SELECT * FROM Students s left outer join StudentExam se ON s.StID = se.StID WHERE se.ExamName=NULL
Results
----
StID StName StID ExamName
3 Bob
(1 row(s) affected)
There's Bob!
WHAT'S GOING ON HERE? How come se.ExamName=NULL evaluates to TRUE but se.ExamName<>'SQL Server' doesn't (with ANSI_NULLS OFF).
Is it because the NULL is "virtual" in the left join. Let's try one more experiment:
Insert Students VALUES (4,NULL)
set ANSI_NULLS OFF
SELECT * FROM STUDENTS WHERE StName = NULL
Results
----
StID StName
4
(1 row(s) affected)
So far so good. Let's try the dratted "not equals to" operator that started this whole mess:
set ANSI_NULLS OFF
SELECT * FROM STUDENTS WHERE StName <> 'Nobody'
Results
----
StID StName
1 Jack
2 Anna
3 Bob
(3 row(s) affected)
WHAT? Where's the new row we just inserted? Must be that pesky "not equals to" operator. Double negative anyone?
set ANSI_NULLS OFF
SELECT * FROM STUDENTS WHERE not StName <> NULL
Results
----
StID StName
4
(1 row(s) affected)
Sure, there it is. So it must not be the "not equals to" operator?
My advice to anyone still reading: BE VERY CAREFUL WITH "SET ANSI_NULLS OFF" AND ANY COMPARISONS WITH NULLS AND BE ESPECIALLY CAREFUL WITH THE <> OPERATOR AND NULLS.