I am surprised to see that IS NULL and =NULL are yielding different results in a select query. What is difference between them? When to use what. I would be glad if you can explain me in detail.
A:
In MSSQL IS NULL is the way to say what you want. = NULL is pretty much never what you mean (and it's really anoying some times).
Wernight
2010-09-23 10:04:06
This doesn't actually answer the question... *why* is IS NULL the right way and *why* is = NULL never what you mean (or annoying)?
Dave
2010-09-23 10:07:52
+2
A:
Check it,
http://www.sqlservercentral.com/articles/T-SQL/understandingthedifferencebetweenisnull/871/
lucky
2010-09-23 10:04:56
+8
A:
= NULL is always unknown (this is piece of 3 state logic), but WHERE clause treats it as false and drops from the result set. So for NULL you should use IS NULL
Reasons are described here: http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server
Andrey
2010-09-23 10:05:35
It is not `false`. It is `unknown`. If it was false then `NOT(X = NULL)` would be `true`. This is not the case. SQL uses 3 valued logic.
Martin Smith
2010-09-23 12:15:57
@Martin Smith i know about 3 state logic. but `WHERE` clause treats `unknown` as `false` and drop off from result set.
Andrey
2010-09-23 12:25:45
+1
A:
Nothing is equal to NULL, not even NULL itself. (NULL = NULL is NULL). =NULL is rarly if ever what you want.
Use IS NULL to check against NULL.
nos
2010-09-23 10:06:12
+4
A:
To add to existing answers, it depends whether you have ANSI_NULLS on or not, when using "= NULL".
-- This will print TRUE
SET ANSI_NULLS OFF;
IF NULL = NULL
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
-- This will print FALSE
SET ANSI_NULLS ON;
IF NULL = NULL
PRINT 'TRUE'
ELSE
PRINT 'FALSE'
AdaTheDev
2010-09-23 10:09:41
+1
A:
because of the three-valued-logic of SQL:
http://en.wikipedia.org/wiki/Null_%28SQL%29#Three-valued_logic_.283VL.29
Markus Winand
2010-09-23 10:10:36