Re: IS NULL against = NULL

Hello Ian,

... and are you sure that the results are the same?

In this example I have one row where id is null: SQL> select count(*) from my_table where id = null;

 COUNT(*)

        0

SQL> select count(*) from a where id is null;

 COUNT(*)

        1

Erika

Ian Meikle 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
Received on Wed Nov 08 2000 - 03:17:13 CST