When I check if a value is NULL I usually use '=', This doesn't work somehow but 'is null' works. Why's that? What is the difference?
Asked
Active
Viewed 138 times
1
-
1Take a look at this question: http://stackoverflow.com/questions/6827010/null-values-in-where-clause – aga Dec 17 '13 at 06:47
-
http://stackoverflow.com/questions/9822154/standard-sql-boolean-operator-is-vs-equals-operator – Darshan Mehta Dec 17 '13 at 06:48
-
http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html – aga Dec 17 '13 at 06:49
-
Think of null as a 'state' of the cell not as a 'content value'. – NoChance Dec 17 '13 at 06:57
-
[take a look here this is one similar](http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server) – vhadalgi Dec 17 '13 at 07:01
3 Answers
1
Have a look at : SQL is null and = null
Basically, null can be thought of as no value or no value know. A comparison operator (eg =) is effectively asking if an unknown or non existent value is equal to another unknown or non existent value. The only sensible return in this case is another unknown or non existent value - null.
IS NULL asks is the value unknown or non existent - this can return either true or false.
Community
- 1
- 1
user2802023
- 21
- 2
1
WHERE myvalue = null
will never be true. Since even if "myvalue" is undefined ("null"), the database can't determine that it equals null (since null by definition means "unknown" and two "unknowns" can't be equal to each other).
WHERE myvalue IS null
is more straight-forward. This checks whether "myvalue" is undefined (is "null" in database lingo).
Lloyd Banks
- 35,740
- 58
- 156
- 248
-
Funny, thanks, I come from Java so it sounded reasonable to me that I check for NULL with '='... – moritzg Dec 17 '13 at 06:59
0
Please see this
http://technet.microsoft.com/en-us/library/ms188048.aspx
you can try the below code. THis is for SQL SEVER
SET ANSI_NULLS OFF
DECLARE @VALUE INT =NULL IF @VALUE=NULL PRINT 'IN 1' IF @VALUE IS NULL PRINT 'IN 2'
Ashutosh Arya
- 1,138
- 2
- 8
- 14