21

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;
Patrick Kostjens
  • 5,065
  • 6
  • 29
  • 46
markus
  • 40,136
  • 23
  • 97
  • 142
  • 1
    @tharkun : I realize you don't like RichB, but realize what your question looks like on the main page if the only two lines you see are SQL code, and not the question. That's why he edited it the way he did, and frankly, his edit made the question look much better. – George Stocker Mar 12 '09 at 18:49
  • @tharkun : Separate his 'harsh' tone from his edits. They are separate. He sure is stark in his language; but that doesn't make his edits any less valid. – George Stocker Mar 12 '09 at 19:16
  • Rolled back to Rich's edits. Not siding with anyone or anything other than the quality of the question. – Jon B Mar 12 '09 at 19:26
  • @tharkun : We discuss this topic daily on irc.freenode.net #stackoverflow. Feel free to join the channel and get to know me (us?) – George Stocker Mar 12 '09 at 19:28

7 Answers7

35
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
Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • that seems to be the pro+ version of what Dana suggests, thanks! – markus Mar 04 '09 at 15:17
  • is that, if I don't want the ordinal in the result set, right?! – markus Mar 04 '09 at 15:23
  • and what's the lonely 't' doing in line 7? – markus Mar 04 '09 at 15:25
  • 2
    Correct: the difference here is that it hides the ordinal. The 't' gives the derived table a name- you could use anything you want, but since we're not using it elsewhere the actual value isn't important. But it won't compile without something there. – Joel Coehoorn Mar 04 '09 at 15:28
  • Since the addition of the ordinal column means there'll be no duplicates, I'd switch to UNION ALL (I know, premature optimization, but it's one I always apply in this situation) – Damien_The_Unbeliever Mar 04 '09 at 15:55
21

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
markus
  • 40,136
  • 23
  • 97
  • 142
Dana
  • 32,083
  • 17
  • 62
  • 73
  • oh, that's brilliant, easy and brilliant! – markus Mar 04 '09 at 15:09
  • Are you sure that order by will not be applied only to the last query in the union? – Kjetil Watnedal Mar 04 '09 at 15:26
  • 1
    Ahh, in SQL Server the ORDER BY applies to the results of the UNION, and not just the second sub-query. – Dana Mar 04 '09 at 16:21
  • The `ORDER BY` applies to the whole UNION, not to the 2nd part. With or withjout brackets and in all 3 DBMS: Postgres, SQL Server, MySQL. Th eonly way o thave it apply in the 2nd part is to use brackets like this: `SELECT ...) UNION (SELECT ...ORDER BY);` But that doesn't mean that the result will be ordered. UNUON does not necessarily preserve the order of the internal subqueries. – ypercubeᵀᴹ Sep 30 '16 at 20:46
5

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.

Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • This seems to work. Makes sense that union will mess up the ordering if it is checking for duplices, while union all will not – David Jun 18 '17 at 21:03
2

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?

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • thanks, I thought so... the tricky ordering problem, if it remainds tricky will be the content of another question which I would link here. – markus Mar 04 '09 at 15:08
1

I know for Oracle there is no way to guarantee which will come out first without an order by. The problem is if you try it it may come out in the correct order even for most of the times you run it. But as soon as you rely on it in production, it will come out wrong.

Thomas Jones-Low
  • 7,001
  • 2
  • 32
  • 36
0

I would have thought not, since the database would most likely need to do an ORDER BY in order to the UNION.

UNION ALL might behave differently, but YMMV.

cagcowboy
  • 30,012
  • 11
  • 69
  • 93
  • UNION ALL simply guarantees that all records are returned. A simple UNION filters duplicates. – Dana Mar 04 '09 at 15:16
-6

The short answer is yes, you will get A then B.

Bill
  • 68
  • 1
  • and what's the long answer, given that everybody else says the opposite? – markus Mar 04 '09 at 15:12
  • The long answer would be that without an ORDER BY clause, you are going to get FIFO based upon the UNION clause. For instance, Table B specified first in the UNION would yeild, B->A. UNION does not involve any sorting behavior in and of itself. – Bill Mar 04 '09 at 15:24
  • ppl don't seem to agree with you, can you prove your claim? – markus Mar 04 '09 at 15:32
  • create table #A (A VARCHAR(10)) create table #B (B VARCHAR(10)) INSERT INTO #A(A) VALUES('A') INSERT INTO #A(A) VALUES('AA') INSERT INTO #A(A) VALUES('AAA') INSERT INTO #B(B) VALUES('B') INSERT INTO #B(B) VALUES('BB') INSERT INTO #B(B) VALUES('BBB') SELECT a from #A UNION SELECT b FROM #B; – Bill Mar 04 '09 at 15:38
  • you'll get: A AA AAA B BB BBB – Bill Mar 04 '09 at 15:40
  • according to the other posters, this doesn't seem to be guaranteed. but thanks for showing the other side of the coin anyways. – markus Mar 04 '09 at 15:48
  • I which rdbms? With low values may be yes but no guaranteed. with a very long table not because may be uses hash and all the order gets broken. – FerranB Mar 04 '09 at 15:53
  • 1
    DBs in general do not guarantee any order for returned results unless a specific order is requested. Coming up with an example that seems to work in some cases does not guarantee that it will always work. – Beska Mar 04 '09 at 16:01