Re: "Union all" and "UNION" ???? Return the same result or not ??
Date: 23 Jan 2004 09:47:16 -0800
Message-ID: <7b208869.0401230947.44caa3a_at_posting.google.com>
Either of the queries will return an arbitrary record. Therefore, you
may get different results by running these queries at different times.
If you tell us what is the required result set of these queries, we
could help you design a statement that not only gives the correct
results every time, but also does so in the most efficient way.
The main problem with both queries is the combination of ORDER BY and
ROWNUM<2. ROWNUMs are assigned after the "where" clause is evaluated,
but *before* the "order by" clause sorts the output. Since your WHERE
allows at most one row to go through, sorting doesn't make much sense.
If you are trying to find the top record, you should place the
subquery (select ... union [all] select ... order by ... ) in an
inline view:
select * from (
select ... where ...
union all
select ... where ...
order by ...)
where rownum<2;
HTH,
Flado
Received on Fri Jan 23 2004 - 18:47:16 CET