Re: most preferable SQL

On Feb 8, 9:33 am, Amritha.Da..._at_gmail.com wrote:
> 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.

Well you understand the difference .. but now you made both queries get the SAME wrong result.

You might want to read this:
http://www.psoug.org/reference/null.html

That said, I cannot give the answer which query would be efficient. In fact if the queries are equivalent, then oracle might rewrite one to be equal to another.

Run both queries and find out which one is efficient.

Anurag Received on Thu Feb 08 2007 - 08:43:41 CST