Re: NO ORDER option
Date: 1998/05/08
Message-ID: <35554cab.19560917_at_192.86.155.100>#1/1
A copy of this was sent to aschneid_at_cmp.com (if that email address didn't require changing) On Fri, 08 May 1998 17:12:18 GMT, you wrote:
>I am trying to do the following statement:
>
>select * from products
>where lower(product) like '%name%' and product_type='S'
>UNION
>select * from products
>where lower(product) like '% name %' and product_type='S'
>
>but the list being returned is not select1 + select2 - it is (select1+select2)
>ordered by the primary key. Isn't there a way to say no order in oracle??
>
>Thanks
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/ Now offering spam-free web-based newsreading
No, its not (select1+select2), its just select1.
SQL> select * from dual
2 union
3 select * from dual;
D
-
X
SQL> select * from dual
2 union ALL
3 select * from dual;
D
-
X
X
SQL> select * from T T1 union select * from T T2 is really saying:
Give me all the rows from T1 and append to that all the rows from T2 that are not already in T1.
In order to do this, Oracle does a sort to apply a filter to the second result set in the union.
If you really just want query2 appended to query1, use UNION ALL which will not sort and not remove dups.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Opinions are mine and do not necessarily reflect those of Oracle Corporation
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 08 1998 - 00:00:00 CEST