Search notes:

SQL: Order of operations - UNION ALL / ORDER BY

create table tq84_tab_1 (id integer, txt varchar(10));
create table tq84_tab_2 (id integer, txt varchar(10));

begin
   insert into tq84_tab_1 values (4, 'four' );
   insert into tq84_tab_1 values (1, 'one'  );
   insert into tq84_tab_1 values (5, 'five' );

   insert into tq84_tab_2 values (6, 'six'  );
   insert into tq84_tab_2 values (7, 'seven');
   insert into tq84_tab_2 values (3, 'three');
end;
/
Github repository about-SQL, path: /select/order-of-operations/union-all_order-by/create-data.sql

Ordering the entire result set

Because in the SQL order of operation, the order by is performed after a union all, the following → development/databases/Oracle/SQL/select statement returns all records ordered by id, not just the records of tq84_tab_2. 
select * from tq84_tab_1 union all
select * from tq84_tab_2
order by
  id;
Github repository about-SQL, path: /select/order-of-operations/union-all_order-by/select-1.sql
Note: Oracle 18c is not able to process this query, it fails with ORA-00904: "ID": invalid identifier.
I was able to run it on SQL Server, MySQL and SQLite, though.

Apply order by first, then combine result set

First attempt

The following example puts the select statements with the order by clauses within parentheses so that the the records of tq84_tab_1 are ordered independently from those of tq84_tab_2. Additionally, the records of tq84_tab_1 are returned first, then the records of tq84_tab_2. 
(select * from tq84_tab_1 order by id) union all
(select * from tq84_tab_2 order by id)
Github repository about-SQL, path: /select/order-of-operations/union-all_order-by/select-2.sql
Unfortunately, I was only able to execute this query without errors on MySQL.
Oracle failed with ORA-00907: missing right parenthesis, SQL Server with Incorrect syntax near the keyword 'order'. and SQLite with Result: near "(": syntax error.

Using subqueries

The order by clause might be put into a subquery in order to achieve the same result on Oracle 18c, MySQL and SQLite. 
select * from (select * from tq84_tab_1 order by id) t union all
select * from (select * from tq84_tab_2 order by id) t;
Github repository about-SQL, path: /select/order-of-operations/union-all_order-by/select-subquery.sql
SQL Server complains with The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified..

Using an additional column

In order to finally make the desired ordering possible in SQL Server, too, an additional column might be added to the select statements: 
select t.*, 1 o from tq84_tab_1 t union all
select t.*, 2 o from tq84_tab_2 t
order by
  o, id
Github repository about-SQL, path: /select/order-of-operations/union-all_order-by/select-additional-col.sql
This also works with SQLite and MySQL, but Oracle fails with ORA-00904: "ID": invalid identifier.

Index