5

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...

Actual Result

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

But I expect...

enter image description here

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.

w5m
  • 2,286
  • 3
  • 34
  • 46
Romesh
  • 2,291
  • 3
  • 24
  • 47
  • 1
    Because null has a special meaning of no value. any comparisons where one side is null will yield null and evaluate to false - read here http://msdn.microsoft.com/en-us/library/ms191504(v=sql.105).aspx – Ian Kenney Jul 03 '13 at 07:40
  • 4
    Anything compared to `NULL` always returns `NULL` (unknown) which is neither true nor false and therefore can't meet your criteria. – Nenad Zivkovic Jul 03 '13 at 07:41

1 Answers1

4

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.

gzaxx
  • 17,312
  • 2
  • 36
  • 54