How to order by with union in SQL?

Standard sort with UNION

A standard way to perform a sort while using UNION is to include the ORDER BY clause after the UNION statement:

SELECT col FROM table1
UNION
SELECT col FROM table2
ORDER BY col;


This will give a result set combining records from both table1 and table2 and sort based on col.

Maintaining original sort with UNION ALL

There can be situations where you would like to keep the sort order from the individual queries before the union. In such cases, use UNION ALL along with an extra sort column:

SELECT col, 1 AS sort_col FROM table1
UNION ALL
SELECT col, 2 FROM table2
ORDER BY sort_col, col;


This ensures the second set of results is appended after the first, retaining the original sort order within each set. It's like serving two dishes on the same platter, while not mixing them up.

Matching column aliases across SELECTs

Make sure the aliases for columns used in the ORDER BY and SELECT clauses match to avoid unwanted surprises:

SELECT col AS alias_name FROM table1
UNION
SELECT col FROM table2 -- same column, no alias needed
ORDER BY alias_name;


In this query, alias_name is used in ORDER BY as a representative for col from both tables.

Handling specific databases

Oracle: Combine subselect, UNION ALL and ORDER BY

In certain Oracle database systems, you will have to use a slightly different approach when combining subselect, UNION ALL, and ORDER BY:

SELECT * FROM (
  SELECT col1 AS result_col FROM table1
  UNION ALL
  SELECT col2 FROM table2
) ORDER BY result_col;


Note that in Oracle, unlike your ex, ORDER BY stays outside the UNION ALL.

Handling different tables

You can UNION results from different tables and still ORDER BY. Just make sure you reference the correct column name:

SELECT col1 AS result_col FROM table1
UNION
SELECT col2 FROM table2 -- assumes col2 is analogous to col1 above
ORDER BY result_col;


It's like ordering from different restaurant menus – as long as you know what you want, you're good to go!

Ordering by column aliases

For query readability, it's a good idea to use aliases for columns and maintain a consistent syntax in all SELECT statements:

SELECT col AS 'SortColumn' FROM table1
UNION ALL
SELECT col AS 'SortColumn' FROM table2
ORDER BY 'SortColumn';


Remember, not everything in SQL is as shady as dark-web transactions. So, great naming conventions are key to clear coding!