Menu
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.
415
It's important to note, that NULL doesn't equal NULL. NULL is not a value, and therefore cannot be compared to another value.
null is well-defined value which may represent an unknown value, but it may also represent the absence of a value. It should be up to the developer to decide what null represents, but null itself is absolutely a value and null is null = null.
In SQL null is not equal ( = ) to anything—not even to another null . According to the three-valued logic of SQL, the result of null = null is not true but unknown. SQL has the is [not] null predicate to test if a particular value is null .
The concept is that NULL is not an equitable value. It denotes the absence of a value. Therefore, a variable or a column can only be checked if it IS NULL , but not if it IS EQUAL TO NULL .
= 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: Why does NULL = NULL evaluate to false in SQL server
51
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' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
