67

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.

satya
  • 1,889
  • 10
  • 29
  • 42
  • 3
    Check out the [wikipedia article](http://en.wikipedia.org/wiki/Null_%28SQL%29) for additional information. – Jeff Mercado Sep 23 '10 at 10:11
  • 1
    This is a dup of a number of questions: http://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server – Paddy Sep 23 '10 at 11:46
  • Does this answer your question? [Why does NULL = NULL evaluate to false in SQL server](https://stackoverflow.com/questions/1843451/why-does-null-null-evaluate-to-false-in-sql-server) – philipxy Mar 03 '23 at 02:12

3 Answers3

63

= 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

Community
  • 1
  • 1
Andrey
  • 59,039
  • 12
  • 119
  • 163
  • 6
    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 Sep 23 '10 at 12:15
  • 2
    @Martin Smith i know about 3 state logic. but `WHERE` clause treats `unknown` as `false` and drop off from result set. – Andrey Sep 23 '10 at 12:25
13

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
  • 142,592
  • 28
  • 206
  • 200
4

There is a difference between IS NULL and = NULL and this is because of the three-valued-logic of SQL:

https://en.wikipedia.org/wiki/Null_%28SQL%29#Three-valued_logic_.283VL.29

Extract of a relevant paragraph:

Challenges

Null has been the focus of controversy and a source of debate because of its associated three-valued logic (3VL), special requirements for its use in SQL joins, and the special handling required by aggregate functions and SQL grouping operators. Computer science professor Ron van der Meyden summarized the various issues as: "The inconsistencies in the SQL standard mean that it is not possible to ascribe any intuitive logical semantics to the treatment of nulls in SQL." Although various proposals have been made for resolving these issues, the complexity of the alternatives has prevented their widespread adoption.

faintsignal
  • 1,828
  • 3
  • 22
  • 30
Markus Winand
  • 8,371
  • 1
  • 35
  • 44
  • 2
    Please do not post link-only answers. Include the relevant information from the link here, so that if the target site disappears, your answer will still be useful. (This is better as a comment.) – jpaugh Feb 03 '16 at 19:49