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

On Feb 8, 8:52 am, Amritha.Da...@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

On Feb 8, 9:24 am, "Anurag Varma" <avora...@gmail.com> wrote:
> On Feb 8, 8:52 am, Amritha.Da...@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.

On Feb 8, 9:33 am, Amritha.Da...@gmail.com wrote:
> On Feb 8, 9:24 am, "Anurag Varma" <avora...@gmail.com> wrote:
>
>
>
> > On Feb 8, 8:52 am, Amritha.Da...@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