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: <Amritha.Datta_at_gmail.com>
Date: 8 Feb 2007 06:33:37 -0800
Message-ID: <1170945217.839509.244850@l53g2000cwa.googlegroups.com>


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?

  1. Update Table1 set Reason = 'SX#' where (Sex <> 'M' and Sex <> 'F' and 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;

Thank you. Received on Thu Feb 08 2007 - 08:33:37 CST

Original text of this message

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