IS NULL against = NULL

From: Ian Meikle <Ian_Meikle_at_attglobal.net>
Date: Wed, 08 Nov 2000 07:26:07 GMT
Message-ID: <3A08FFE7.5EB26F5C@attglobal.net>

Hello,

From my short time doing SQL I did not know it was possable to have an operator equal (=) to NULL, i.e.

WHERE SOME_COLUMN = NULL I have always written it

WHERE SOME_COLUMN IS NULL However, I ran the following SQL

SELECT COUNT(*) FROM SOME_TABLE WHERE SOME_COLUMN IS NULL and got the following explain plan:

SELECT STATEMENT Optimizer=CHOOSE (Cost=382 Card=1 Bytes=34) SORT (AGGREGATE)
TABLE ACCESS (FULL) OF SOME_TABLE (Cost=382 Card=1 Bytes=34)

I found this kind of strange since I have a unique index on SOME_COLUMN

Then I ran the SQL as:

SELECT COUNT(*) FROM SOME_TABLE WHERE SOME_COLUMN = NULL I got the following explain plan:

SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=34) SORT (AGGREGATE)
INDEX (UNIQUE SCAN) OF I_SOME_COLUMN (UNIQUE) (Cost=1 Card=1 Bytes=34)

and the query ran about 100 times faster (obviously)

Can anyone explain this ??

Cheers
Ian Received on Wed Nov 08 2000 - 01:26:07 CST