2
SELECT a FROM b
UNION ALL 
SELECT a FROM c
UNION ALL 
SELECT a FROM d

Does UNION ALL guarantee to print out records from tables b, c, d in that order? I.e., no records from c before any from b. This question is not for a specific DBMS.

OMG Ponies
  • 314,254
  • 77
  • 507
  • 490
st12
  • 2,716
  • 4
  • 26
  • 42
  • 1
    `ORDER BY` is the *only* way to guarantee the order of results in a query in standard SQL. – Gabe Sep 05 '10 at 05:23
  • possible duplicate of [SQL Server UNION - What is the default ORDER BY Behaviour](http://stackoverflow.com/questions/421049/sql-server-union-what-is-the-default-order-by-behaviour) – 200_success May 08 '14 at 21:26

2 Answers2

8

No order by, no order guarantee whatsoever - that's for every database.

And for standard SQL, an ORDER BY is applied to the results from all the unioned queries.

OMG Ponies
  • 314,254
  • 77
  • 507
  • 490
1

To be sure in order use 

Select 1 as TableNo,* from a
union all 
select 2 as TableNo,* from b
union all
select 3 as TableNO,* from c
order by TableNo, [desired column]
marc_s
  • 704,970
  • 168
  • 1,303
  • 1,425
adopilot
  • 4,160
  • 12
  • 63
  • 92