Re: IS NULL against = NULL

In article <3A08FFE7.5EB26F5C_at_attglobal.net>,   Ian Meikle <Ian_Meikle_at_attglobal.net> wrote:
> 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
>
>

Using = NULL won't provide correct results as NULL is nothing and can't be compared to anything else. You will not be able to use the equality operator with NULL. It's not proper sql either. You are getting the different plans because you don't use the equality operator in 1 and do use the equality operator 2. However your second statement is incorrect and you will be bound to sue a full table scan.

Hth,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.