Union all: Order by coalesce (without subquery)

Question

I have two tables that I am unioning together: WORKORDER_1 and WORKORDER_2.

--fake tables in WITH clause

with workorders_1 as (
select 'WO1' as wonum,      null as parent from dual
union all
select 'WO100' as wonum, 'WO1' as parent from dual
union all
select 'WO100' as wonum, 'WO1' as parent from dual
),

workorders_2 as (
select 'WO200' as wonum, 'WO2' as parent from dual
union all
select 'WO200' as wonum, 'WO2' as parent from dual
union all
select 'WO2' as wonum, null as parent from dual
)

select * from workorders_1
union all
select * from workorders_2

WONUM    PARENT
-----    ------
WO1         
WO100    WO1   
WO100    WO1   
WO200    WO2   
WO200    WO2   
WO2  

I want to sort the unioned tables by the coalesced parent ID:

• If PARENT is not null, use it
• Else, use WONUM

---

The result would look like this:

WONUM    PARENT    [order by/coalesce]
-----    ------    ----------
WO1                [WO1]
WO100    WO1       [WO1]
WO100    WO1       [WO1]

WO2                [WO2]
WO200    WO2       [WO2]
WO200    WO2       [WO2]

I would prefer to do the order-by without wrapping the queries as a subquery.

• While the sample queries in this question are simple, my real queries are already quite lengthy, and adding a subquery would make it even more difficult to read.

Is there a way order by using coalesce-like-logic — without using a subquery?

Answer 1

You can use coalesce() in the order by clause. For such expression, Oracle requires that you wrap the union query in a subquery:

select *
from (
    select * from workorders_1
    union all
    select * from workorders_2
) t
order by coalesce(parent, wonum), wonum

Demo on DB Fiddle