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;
views:
844answers:
7
+1
A:
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
2009-03-04 15:05:37
+2
A:
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
2009-03-04 15:05:49
thanks, I thought so... the tricky ordering problem, if it remainds tricky will be the content of another question which I would link here.
tharkun
2009-03-04 15:08:23
+7
A:
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
Dana
2009-03-04 15:08:17
Are you sure that order by will not be applied only to the last query in the union?
Kjetil Watnedal
2009-03-04 15:26:15
Ahh, in SQL Server the ORDER BY applies to the results of the UNION, and not just the second sub-query.
Dana
2009-03-04 16:21:09
A:
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
2009-03-04 15:09:22
UNION ALL simply guarantees that all records are returned. A simple UNION filters duplicates.
Dana
2009-03-04 15:16:30
+11
A:
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
2009-03-04 15:09:23
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
2009-03-04 15:28:38
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
2009-03-04 15:55:09
and what's the long answer, given that everybody else says the opposite?
tharkun
2009-03-04 15:12:52
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
2009-03-04 15:24:54
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
2009-03-04 15:38:11
according to the other posters, this doesn't seem to be guaranteed. but thanks for showing the other side of the coin anyways.
tharkun
2009-03-04 15:48:35
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
2009-03-04 15:53:38
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
2009-03-04 16:01:41
A:
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
2009-03-04 15:11:45