Is There Any Difference Between Is Null and =Null

Is there any difference between IS NULL and =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

SQL is null and = null

In SQL, a comparison between a null value and any other value (including another null) using a comparison operator (eg =, !=, <, etc) will result in a null, which is considered as false for the purposes of a where clause (strictly speaking, it's "not true", rather than "false", but the effect is the same).

The reasoning is that a null means "unknown", so the result of any comparison to a null is also "unknown". So you'll get no hit on rows by coding where my_column = null.

SQL provides the special syntax for testing if a column is null, via is null and is not null, which is a special condition to test for a null (or not a null).

Here's some SQL showing a variety of conditions and and their effect as per above.

create table t (x int, y int);
insert into t values (null, null), (null, 1), (1, 1);

select 'x = null' as test , x, y from t where x = null
union all
select 'x != null', x, y from t where x != null
union all
select 'not (x = null)', x, y from t where not (x = null)
union all
select 'x = y', x, y from t where x = y
union all
select 'not (x = y)', x, y from t where not (x = y);

returns only 1 row (as expected):

TEST    X   Y
x = y   1   1

See this running on SQLFiddle

Is there a difference between IS NULL and IS NOT DISTINCT FROM NULL?

The IS NOT DISTINCT FROM predicate is part of the SQL standard (SQL:2003)

However, it isn't yet fully adopted by all the DBMS.

Actually, only a few... Well, PostgreSql has.

You can see it as a "NULL tolerant equal"

To compare:

(1 = 1)  --> true
(1 = 0)  --> false
(1 = null) --> unknown/null
(null = null) --> unknown/null

(1 IS NULL) --> false
(null IS NULL) --> true

(1 IS NOT DISTINCT FROM 1) --> true
(1 IS NOT DISTINCT FROM 0) --> false
(1 IS NOT DISTINCT FROM null)  --> false
(null IS NOT DISTINCT FROM null)  --> true

So the main difference between IS NULL versus IS NOT DISTINCT FROM?

Basically, the IS NULL is used to check if an element is empty.

While IS NOT DISTINCT FROM compares 2 elements.

Used in a WHERE clause then this:

WHERE (x IS NOT DISTINCT FROM y)

Has a Standard SQL alternative:

WHERE (x = y OR (x IS NULL AND y IS NULL))

Difference between is not null and Null in SQL Server?

1) First question about difference IS NULL vs = NULL:

Comparison operators like (=, <>, <, >, ...) with NULL always produce NULL.
Use IS NULL/IS NOT NULL instead.

2) Second question "why (null = null) is false":

From SQL and the Snare of Three-Valued Logic:

One kind of NULL marks values which are:

missing because the value is
unknown

and the other kind marks values that are missing because the
attribute is missing.

When you try to compare NULL you actualy do something like

UNKNOWN = UNKNOWN

This is of course unknown.

What is the difference between x is null and x == null ?

Update: The Roslyn compiler has been updated to make the behavior of the two operators the same when there is no overloaded equality operator. Please see the code in the current compiler results (M1 and M2 in the code) that shows what happens when there is no overloaded equality comparer. They both now have the better-performing == behavior. If there is an overloaded equality comparer, the code still differs.

See for older versions of the Roslyn compiler the below analysis.

---

For null there isn't a difference with what we are used to with C# 6. However, things become interesting when you change null to another constant.

Take this for example:

Test(1);

public void Test(object o)
{
    if (o is 1) Console.WriteLine("a");
    else Console.WriteLine("b");
}

The test yields a. If you compare that to o == (object)1 what you would have written normally, it does make a lot of difference. is takes into consideration the type on the other side of the comparison. That is cool!

I think the == null vs. is null constant pattern is just something that is very familiar 'by accident', where the syntax of the is operator and the equals operator yield the same result.

---

As svick commented, is null calls System.Object::Equals(object, object) where == calls ceq.

IL for is:

IL_0000: ldarg.1              // Load argument 1 onto the stack
IL_0001: ldnull               // Push a null reference on the stack
IL_0002: call bool [mscorlib]System.Object::Equals(object, object) // Call method indicated on the stack with arguments
IL_0007: ret                  // Return from method, possibly with a value

IL for ==:

IL_0000: ldarg.1              // Load argument 1 onto the stack
IL_0001: ldnull               // Push a null reference on the stack
IL_0002: ceq                  // Push 1 (of type int32) if value1 equals value2, else push 0
IL_0004: ret                  // Return from method, possibly with a value

Since we are talking about null, there is no difference since this only makes a difference on instances. This could change when you have overloaded the equality operator.

What is the difference between null and (type)null?

Look at this example project:

static void Main(string[] args)
{
    //Error: The call is ambigious between the following methods or properties: ShowMessage(FirstClass), ShowMessage(SecondClass)
    ShowMessage(null);
    //Here it is known which type is being used
    ShowMessage((FirstClass)null);
}

private static void ShowMessage(FirstClass value)
{
    System.Console.WriteLine(value);
}

private static void ShowMessage(SecondClass value)
{
    System.Console.WriteLine(value.ToString());
}

class FirstClass { }
class SecondClass { }

Therefor, null and (FirstClass)null is not the same. With (FirstClass)null you define it explicitly as the nullable type FirstClass (class is nullable as reference type) that is null. The other null is just ... well null and must be cast implicitly to the needed nullable type.

Beware:
If you'd replace

ShowMessage(NotFirstClassObj as FirstClass)

with

ShowMessage(NotFirstClassObj is Firstclass ? (FirstClass)NotFirstClassObj : null)

It still works because the compiler can still derive the correct type. But this may be the reason why it's (type)null in the documentary.

What's the difference between is null AND = NULL

NEVER check for nulls using foo = null or foo <> null or foo != null

mysql> SELECT 1 <> NULL;
        -> NULL

Not even NULL is equal to NULL!

mysql> SELECT NULL = NULL;
        -> NULL

Instead use one of the following operators

---

The <=> is the Null-Safe Operator

NULL-safe equal. This operator performs an equality comparison like the = operator, but returns 1 rather than NULL if both operands are NULL, and 0 rather than NULL if one operand is NULL.

mysql> SELECT 1 <=> 1, NULL <=> NULL, 1 <=> NULL;
        -> 1, 1, 0
mysql> SELECT 1 = 1, NULL = NULL, 1 = NULL;
        -> 1, NULL, NULL

---

On the other hand, IS NULL is a little more straight forward

Tests whether a value is NULL.

mysql> SELECT 1 IS NULL, 0 IS NULL, NULL IS NULL;
        -> 0, 0, 1

Important: Read the IS NULL documentation to see how the sql_auto_is_null setting affects this operator.

See also: IS NOT NULL to test for values not equal to NULL.

---

You might be interested in COALESCE too.

what is difference between null and null of String.valueOf(String Object)

Here's the source code of String.valueOf: -

public static String valueOf(Object obj) {
    return (obj == null) ? "null" : obj.toString();
}

As you can see, for a null value it returns "null" string.

So,

String stringValue = null;
String valueOf = String.valueOf(stringValue);

gives "null" string to the valueOf.

Similarly, if you do: -

System.out.println(null + "Rohit");

You will get: -

"nullRohit"

---

EDIT

Another Example:

Integer nulInteger = null;
String valueOf = String.valueOf(nulInteger) // "null"

But in this case.

Integer integer = 10;
String valueOf = String.valueOf(integer) // "10"

---

Related Topics