| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: IS NULL against = NULL
Wonder why the "= NULL" isn't flagged as an error? I've been stung by this in the past. Note that "=NULL" does work with sybase and also sqlserver!
Go figure, Bob
<sybrandb_at_my-deja.com> wrote in message news:8ub6lc$6ng$1_at_nnrp1.deja.com...
> 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
> >
> >
>
>
>
>
> >
 |
 |