Where Clause Rejecting Rows if NULL occurred

Question

A Theory Question...

When a set of queries as given below is fired then...

Create table Temp1(C1 varchar(2))
Create table Temp2(C1 varchar(2))
insert into Temp1 Values('A'),(NULL),('B')
insert into Temp2 Values('B'),(NULL),('C'),(NULL)

select *from Temp1 A,Temp2 B
where A.C1 <> B.C1

...gives...

I used A.C1 <> B.C1 in the Where clause.

---

But I expect...

To get expected result as output I needed to use ISNULL(A.C1,'') <> ISNULL(B.C1,'') in the Where clause.

My Question is why do I need to use ISNULL every time for getting output as expected, as NULL is not equal to any string data.

Answer 1

Quote from here is perfect:

The correct way to understand NULL is that it is not a value. Not “this is a NULL value” but “this NULL is not a value.” Everything either is a value, or it isn’t. When something is a value, it is “1,” or “hello,” or “green,” or “$5.00″ etc — but when something isn’t a value, it just isn’t anything at all. SQL represents “this has no value” by the special non-value NULL. When someone says “the NULL value,” one should mentally disagree, because there’s no such thing. NULL is the complete, total absence of any value whatsoever.

Null is not equal to anything, therefore comparison always fails.

For example try this query:

select *
from Temp2 B
where B.C1 = null 

it won't return any row!

The handle nulls you have to use is null or is not null:

select *
from Temp1 A, Temp2 B
where A.C1 <> B.C1 or (A.C1 is null and B.C1 is not null) or (B.C1 is null and A.C1 is not null)

return exactly the same values that your query with ISNULL returns.