Re: IS NULL against = NULL

Hello Erika,

You are right, it was just chance that the result was the same this time. When I tested further, I got differing results.

Also when I read further into the Oracle 8 SQL Reference manuals it explained it better to me.

Thanks for your help
Ian

Erika Grondzakova wrote:

> 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 Thu Nov 09 2000 - 05:43:18 CST