CODEWITHSUNDEEP
sqlsql-server
Ankush Madankar
Ankush Madankar

Reputation: 3834

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

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.

Upvotes: 7

Views: 22650

Answers (4)

Lasse V. Karlsen
Lasse V. Karlsen

Reputation: 391326

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.

Upvotes: 17

John Warlow
John Warlow

Reputation: 2992

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

Upvotes: 3

SamuraiJack
SamuraiJack

Reputation: 5539

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

Upvotes: 2

Jainendra
Jainendra

Reputation: 25143

= 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

Upvotes: 2

Related Questions