How can I order entries in a UNION without ORDER BY?

Question

How can I be sure that my result set will have a first and b second? It would help me to solve a tricky ordering problem.

Here is a simplified example of what I'm doing:

SELECT a FROM A LIMIT 1 
UNION 
SELECT b FROM B LIMIT 1;

Answer 1

SELECT col
FROM 
   (
       SELECT a col, 0 ordinal FROM A LIMIT 1
       UNION ALL
       SELECT b, 1 FROM B LIMIT 1
   ) t
ORDER BY ordinal

Answer 2

I don't think order is guaranteed, at least not across all DBMS.

What I've done in the past to control the ordering in UNIONs is:

(SELECT a, 0 AS Foo FROM A LIMIT 1)
UNION
(SELECT b, 1 AS Foo FROM B LIMIT 1)
ORDER BY Foo

Answer 3

Your result set with UNION will eliminate distinct values.

I can't find any proof in documentation, but from 10 years experience I can tell that UNION ALL does preserve order, at least in Oracle.

Do not rely on this, however, if you're building a nuclear plant or something like that.

Answer 4

No, the order of results in a SQL query is controlled only by the ORDER BY clause. It may be that you happen to see ordered results without an ORDER BY clause in some situation, but that is by chance (e.g. a side-effect of the optimiser's current query plan) and not guaranteed.

What is the tricky ordering problem?