SQL: how to use UNION and order by a specific select?

Question

I have two selects:

SELECT id FROM a -- returns 1,4,2,3
UNION
SELECT id FROM b -- returns 2,1

I'm receiving correct num of rows, like: 1,4,2,3.

But I want b table results first: 2,1,4,3 or 2,1,3,4

How can I do this?

(I'm using Oracle)

Answer 1

You want to do this:

select * from 
(
    SELECT id, 2 as ordered FROM a -- returns 1,4,2,3
    UNION
    SELECT id, 1 as ordered FROM b -- returns 2,1
)
order by ordered

Update

I noticed that even though you have two different tables, you join the IDs, that means, if you have 1 in both tables, you are getting only one occurrence. If that's the desired behavior, you should stick to UNION. If not, change to UNION ALL.

So I also notice that if you change to the code I proposed, You would start getting both 1 and 2 (from both a and b). In that case, you might want to change the proposed code to:

select distinct id from 
(
    SELECT id, 2 as ordered FROM a -- returns 1,4,2,3
    UNION
    SELECT id, 1 as ordered FROM b -- returns 2,1
)
order by ordered