Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: most preferable SQL

Re: most preferable SQL

From: Anurag Varma <avoracle_at_gmail.com>
Date: 8 Feb 2007 06:24:31 -0800
Message-ID: <1170944671.389459.214840@l53g2000cwa.googlegroups.com>


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 Received on Thu Feb 08 2007 - 08:24:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US