Difference between "=" and "is" in sql server

Question

I am having problem while understanding = and is operators in SQL Server.

Consider the following example queries which are having different behaviors in their respective output:

SELECT * FROM tableName WHERE colName IS NULL;
SELECT * FROM tableName WHERE colName = NULL;

First query will provide the required output i.e. select those records for which colName is having a null value. But the second query will result in zero matching records.

Please clarify different uses of these operators with pros and cons.

EDIT

Here, most of the answers are claiming that = doesn't work with null, but the following statement will work with null and =.

SET ANSI_NULLS OFF
SELECT * FROM tableName WHERE colName = NULL;

This will provide the same result as statement having is operator.

It generally helps to read the documentation. [= (Equals) (Transact-SQL)](http://msdn.microsoft.com/en-us/library/ms175118.aspx) — , Sep 03 '14 at 10:17
You should take at look at http://stackoverflow.com/questions/1833949/why-is-null-not-equal-to-null-false — Davz, Sep 03 '14 at 10:19
Note though that in a future version of SQL server, `ANSI_NULLS` will always be `ON` and `SET ANSI_NULLS_OFF` will return an error : http://msdn.microsoft.com/en-us/library/ms188048.aspx — John Warlow, Sep 03 '14 at 10:32
The use of ANSI_NULLS OFF is a kind of hack. Non-standard. Microsoft explicitely tells us not to use it here: http://msdn.microsoft.com/en-us/library/ms188048.aspx . So yes, with ANSI_NULLS OFF the equal sign gives you different results, which is horrible and should be avoided. — Thorsten Kettner, Sep 03 '14 at 10:33
MySql, btw, has a nice extension to the SQL standard here: The null-safe equality operator <=> lets you explicitely ask to treat null as equal to another null. (And other than ANSI_NULLS OFF in T-SQL it also treats col1 and col2 as equal when both contain null.) — Thorsten Kettner, Sep 03 '14 at 10:45
[This answer](http://stackoverflow.com/a/17534829/621962) seems relevant. — canon, Sep 04 '14 at 20:44

score 17 · Accepted Answer · edited Sep 03 '14 at 10:42

17

Nothing equals null.

Not even null equals null.

null is not a value, it is more like a concept, or a mark, meaning unknown value.

As such, you need two operators for this, one for equality, and one for checking the concept of null.

Once you start to think of null as "unknown value" a lot of the other behavior also makes sense.

10 + null? Add an unknown value to 10? Obviously you will have another unknown value as a result.

For more information, please check the documentation of the equality operator in T-SQL.

Additionally, see the documentation for SET ANSI_NULL.

Note that the documentation is in conflict about the behavior of x = null between the equality operator (documentation says it will always be false if x is non-null) whereas SET ANSI_NULLS documentation says that x = null will behave equivalent to x is null when the option is turned on.

edited Sep 03 '14 at 10:42

John Warlow

2,922
1
34
49

answered Sep 03 '14 at 10:16

Lasse V. Karlsen

380,855
102
628
825

1

`0 * null`? Multiply 0 by an unknown value? Obviously you will have 0 as a result. Except that you don't. – Sep 03 '14 at 10:19
1

If you were to multiply 0 by infinity you would get what is known as "indeterminate form" since infinity is not a number. There could be similar reasons to why the result in SQL is also unknown. – Lasse V. Karlsen Sep 03 '14 at 10:21
`0 * null` is a multiplication of two `int` (non-)values, and `int` does not have infinities. I think reading `null` as "unknown value" is largely useful, but not entirely correct. That said, I don't have a better explanation. – Sep 03 '14 at 10:24
by changing sql setting to: SET ANSI_NULLS OFF, will result in comparison with null value, see edited question – Ankush Madankar Sep 03 '14 at 10:29
I can see that it happens, but the documentation says otherwise. I would not rely on that functionality unless the documentation is changed, for all we know it is a bug that is waiting to be fixed. – Lasse V. Karlsen Sep 03 '14 at 10:33
Just saw that the two pieces of documentation is in conflict so added a note to that fact. – Lasse V. Karlsen Sep 03 '14 at 10:36
1

I've also added a community note on the equality operator as I believe that's the one at fault (documentation-wise). – Lasse V. Karlsen Sep 03 '14 at 10:39
I don't think that part of the documentation is wrong. If `x` is a non-null value, then `x is null` is false, so saying that `x = null` returns false is equivalent to saying `x = null` returns the same as `x is null`. What is wrong is that the documentation is not making a difference between false and unknown results. – Sep 03 '14 at 10:55

John Warlow · Answer 2 · 2014-09-03T10:36:06.670

From http://msdn.microsoft.com/en-us/library/ms188795.aspx:

To determine whether an expression is NULL, use IS NULL or IS NOT NULL instead of comparison operators (such as = or !=). Comparison operators return UNKNOWN when either or both arguments are NULL.

EDIT

The originating question was updated to note that SET ANSI_NULLS OFF allows:

select * from tableName where colName = null.

This may be true at the moment but future versions of SQL server will set ANSI_NULLS always ON and any calls to SET ANSI_NULLS OFF will result in an error.

Source: http://msdn.microsoft.com/en-us/library/ms188048.aspx

score 2 · Answer 3 · answered Sep 03 '14 at 10:18

= 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

= NULL is used for assignment to a NULL value whereas IS NULL is used to determine whether a variable is NULL-valued.

See these articles on null

Wikipedia NUll (SQL)
w3schools SQL NULL Values
SQL Tutorial, see IS NULL Operator section

score 2 · Answer 4 · answered Sep 03 '14 at 10:34

Null is not same as zero. Null is absence of value. It simply means that the value could be anything. It is unknown.

Q. Why select * from tableName where colName = null returns zero rows?

A. Because you can not be sure that one null(unknown value) is equal to or not equal to another null(another unknown value).

EG: I have a magic hat and nobody knows what will come out of it(if anything comes out at all). You have another magic hat and nobody knows what will come out of it(if anything comes out at all).

We both have a magic hat each and what it has inside is unknown (null). These both hats could contain a rabbit each or may be my hat contains a hammer and your's has a pineapple.

If you have an if condition like this..

if(@flag<>null)

it is a mistake because if you pass null to @flag you do not really know whether or not @flag is equal or not equal to null

Use if(@flag<>isnull(null,'')) instead

Difference between "=" and "is" in sql server

4 Answers4

Linked