Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: most preferable SQL
On Feb 8, 9:24 am, "Anurag Varma" <avora..._at_gmail.com> wrote:
> On Feb 8, 8:52 am, Amritha.Da..._at_gmail.com wrote:
>
>
>
> > Which is most preferable between the below SQLs? In terms of consuming
> > resources or fast execution.
>
> > 1. Update Table1 set Reason = 'SX#' where
> > (Sex <> 'M' or
> > Sex <> 'F' or
> > trim(Sex) <> '') and
> > Key = inKey And
> > Sub_Key = inSubKey and
> > Reason is NULL;
>
> > 2. Update Table1 set Reason = 'SX#' where
> > (Sex not in ( 'M', 'F','') and
> > Key = inKey And
> > Sub_Key = inSubKey and
> > Reason is NULL;
>
> > I am calling this SQL in the stored procedure. If there is another
> > better way of writing SQL for the above query, please let me know.
>
> > Thanks in advance.
>
> > Amrith
>
> Those two statements are not equivalent.
>
> ORA92> select 1 from dual where 'A' not in ('','F');
>
> no rows selected
>
> ORA92> select 1 from dual where ('A' <> 'F' or 'A' <> '');
>
> 1
> ----------
> 1
>
> Be careful when dealing with nulls.
> And once you sort out the null issue in your queries,
> try testing them yourself to find the answer.
>
> Anurag
Thanks Anurag. Now I understand the difference. If I use 'and' instead of 'or which query is efficient?
Thank you. Received on Thu Feb 08 2007 - 08:33:37 CST