If I do 4 selects in order 1, 2, 3, 4, using UNION ALL, the results come back 1, 4, 3, 2. Why? I can use ORDER BY to rearrange the order, but why is it in this seemingly "out of order" order by default? Use this table:
Use this SELECT block:
Results:
|
Unlike some other languages - e.g. C/C++ - the SQL language does not guarentee the order of operations taken to execute a statement. As such, the SQL optimizer and execution engine is allowed to perform the operations in any order that it sees fit, presumably in an attempt to execute the statement as quickly as possible. You did not specify the version (and build number) that you are using so it is difficult to determine why you got the output that you did - possibly due to a parallel execution plan or perhaps an artifact in the way the statement is parsed - but as Seth has mentioned, the only way that you can guarantee the ordering of the output is to use an ORDER BY clause. |
Version 12.0 EBF 2601