SQL Server Performance Forum – Threads Archive

IS NULL vs. = NULL

How do you explain the different results between the following statements: –**************STATEMENT #1 ****************************
DECLARE @GUID UNIQUEIDENTIFIER
SET @GUID = NULL
SELECT COUNT(*) FROM MyTable WHERE GUID = @GUID
GO The return count for the above statement = 0
–******************************************************* –**************STATEMENT #2 ****************************
SELECT COUNT(*) FROM MyTable WHERE GUID IS NULL
GO The return count for the above statement = 856,749
–*******************************************************

IS NULL is a way for searching specifically for NULLS,
= is an equality operator, and in SQL NULL does not equal NULL since it is ‘undefined’ Cheers
Twan

That’s actually expected behaviour and the way NULLs work. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

if (null = null) print ‘NULL=NULL’
else if (null is null) print ‘NULL IS NULL’ Kazi Hassan

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by kazi if (null = null) print ‘NULL=NULL' else if (null is null) print ‘NULL IS NULL' Kazi Hassan <hr height="1" noshade id="quote"></blockquote id="quote"> This remainds me that I already answered this type of question sometime back [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

Actually this question comes up every now and then. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

]]>