Oracle union default sort is weird As you know union removes duplicated rows. I thought it removes from the latter ones, but it doesn't.

I found out that if there's no order by clause, Oracle sorts the merged dataset by the first column and removes the duplicates.

select 4,5,6 from dual
union 
select 1,2,3 from dual
union 
select 4,5,6 from dual


It results :

1   2   3
4   5   6


Whereas I expect :

4   5   6
1   2   3


I failed many times with this, and make the problem simple like the example above.

How do I keep the union sequence without order by?

In my real case, the first select is full match(=) result and the second is partial match(like) result. 

If I search for roma for example.

Full match result : roma, partial match result : aroma.

And of course full match result should come first. As I said, it seems order by the first column of union result. 

But, as you see:

select 'roma' from dual -- result comes out anyway..
union select 'aroma' from dual;


Result:

aroma
roma


aroma is alphabetically ahead of roma, it comes first.

Maybe I should try union all and remove the duplicates?
The simple answer is....

To enforce an order in ANY resultset, not just your specific case, you have to use an order by clause.

The Oracle documentation spells it out clearly:


  Use the ORDER BY clause to order rows returned by the statement.
  Without an order_by_clause, no guarantee exists that the same query
  executed more than once will retrieve rows in the same order.