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
  • 325,700
  • 82
  • 523
  • 502
u17
  • 2,776
  • 4
  • 31
  • 43
  • 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
  • 325,700
  • 82
  • 523
  • 502
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
  • 732,580
  • 175
  • 1,330
  • 1,459
adopilot
  • 4,340
  • 12
  • 65
  • 92