Is that sort order from subselect guaranteed?

user_802058 0
Viewed 69

Is it guaranteed that the sort order from statement's

SELECT nr
FROM
(
    SELECT 1 AS nr FROM dual
    UNION
    SELECT 2 AS nr FROM dual
    UNION
    SELECT 3 AS nr FROM dual
);

result set is always

1
2
3

?

oracle sql
2 Answers

No, this is not guaranteed. SQL tables represent unordered sets. SQL results sets are also unordered -- unless the code has an explicit ORDER BY.

With no ORDER BY, you will get the same three rows, but the values could be in any order. I would strongly recommend that you do the following:

SELECT nr
FROM (SELECT 1 AS nr FROM dual
      UNION ALL
      SELECT 2 AS nr FROM dual
      UNION ALL
      SELECT 3 AS nr FROM dual
     ) x
ORDER BY nr;

Note that this uses UNION ALL rather than UNION. UNION incurs overhead for removing duplicates -- something not needed for this data.

user_11440350

Use order by -- then it is guaranteed

SELECT nr
FROM
(
    SELECT 1 AS nr FROM dual
    UNION
    SELECT 2 AS nr FROM dual
    UNION
    SELECT 3 AS nr FROM dual
)a order by nr
user_69148640
Related