How to use order by with union all in SQL?

Fitting more complexity into ORDER BY

Adding a sortby column for managing sort order across different tables:

SELECT columnA, 1 as sortby FROM tableX --tableX took the first shot, or shall we say...sort!
UNION ALL
SELECT columnB, 2 as sortby FROM tableY --tableY keeping up, at sortby 2!
ORDER BY sortby, columnA;  --Let the sorting games begin!


Teams up different tables with custom sorting directives using sortby.

Indulging alias for more efficient ORDER BY

Maintain alias consistency for concise ORDER BY:

SELECT columnA as SortColumn FROM tableX --tableX ships out SortColumn
UNION ALL
SELECT columnB as SortColumn FROM tableY --tableY answers with its own SortColumn
ORDER BY SortColumn;  --and the SortColumn takes it home!


Column alias SortColumn employed for ordering the joined tables.

Autonomy in UNION set sorting

To sort each union set before merging:

SELECT * FROM (
    SELECT TOP 100 PERCENT columnA, otherColumn FROM tableX ORDER BY otherColumn --tableX nails the sorting 
    UNION ALL
    SELECT TOP 100 PERCENT columnA, otherColumn FROM tableY ORDER BY otherColumn --tableY, every bit as sorted
) AS combined --two sorted souls combined
ORDER BY columnA;  --And then columnA said... let there be order!


Individual union sets sorted using a TOP keyword before combining.

Sidestepping pitfalls

Abstain from erroneous usage of ORDER BY with UNION ALL to evade errors. Be especially cautious with parentheses usage, and keep aliases well-defined.

SQL dialects and choosing the efficient ORDER BY

Oracle SQL optimizes ORDER BY using column numbers:

SELECT columnA, columnB FROM tableX --tableX declares A and B
UNION ALL
SELECT columnA, columnB FROM tableY --tableY counts two, how about you?
ORDER BY 1, 2; --Numbers, the universal language!


Subqueries for your sorting pleasure

Try subqueries for complex sorts:

SELECT * FROM (
    SELECT columnA, 'X' as source FROM tableX --X marks the source!
    UNION ALL
    SELECT columnA, 'Y' as source FROM tableY --Y not?
) AS combined --X or Y, we're still good ol' combined
ORDER BY CASE WHEN source = 'X' THEN 1 ELSE 2 END, columnA; --Choose your loyalty, X or Y?


Custom sort logic outside of UNION ALL for more refined results.

Going custom with ORDER BY

Paint by numbers: row numbers to the rescue!

Add ROW_NUMBER() for sequential sorting:

SELECT * FROM (
    SELECT ROW_NUMBER() OVER(ORDER BY columnA) as RN, * FROM tableX --tableX now with more numbering!
    UNION ALL
    SELECT ROW_NUMBER() OVER(ORDER BY columnA) as RN, * FROM tableY --tableY follows suit with row numbers!
) AS combined
ORDER BY RN; --RN them all!


Row numbers originating from each SELECT, sorting joined data more naturally.

Keeping data type consistency

Ensure columns for sorting share same data type:

SELECT columnA, CAST(otherColumn AS INT) as sortableColumn FROM tableX --sortableColumn now in integer style!
UNION ALL
SELECT columnB, CAST(anotherColumn AS INT) as sortableColumn FROM tableY --sortableColumn too neat to miss!
ORDER BY sortableColumn; --sortableColumn keeps sorting casual


Casting removes inconsistencies when matching up columns from different tables.

Workarounds for DBMS restrictions

Sorting post UNION ALL can bring troubles depending on your SQL implementation. Wrappings can help:

SELECT * FROM (
    SELECT * FROM (
        SELECT columnA FROM tableX --tableX in style, A game strong!
        UNION ALL
        SELECT columnA FROM tableY --tableY unwrapped, gifts sorted!
    ) AS combined_inside --a combined surprise inside
) AS combined_outside --all goodness, combined and sorted!
ORDER BY columnA; 


Wrapping allows sorting after UNION ALL across SQL dialects.