@Adrien's answer is not working. It gives an ORA-01791.
The correct answer (for the question that is asked) should be:
select id
from
(SELECT id, 2 as ordered FROM a -- returns 1,4,2,3
UNION ALL
SELECT id, 1 as ordered FROM b -- returns 2,1
)
group by id
order by min(ordered)
Explanation:
This solves all the cases, even when table b has more or different elements then table a
SELECT id, 1 AS sort_order
FROM b
UNION
SELECT id, 2 AS sort_order
FROM a
MINUS
SELECT id, 2 AS sort_order
FROM b
ORDER BY 2;
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
@Adrian's answer is perfectly suitable, I just wanted to share another way of achieving the same result:
select nvl(a.id, b.id)
from a full outer join b on a.id = b.id
order by b.id;
SELECT id FROM a -- returns 1,4,2,3
UNION
SELECT id FROM b -- returns 2,1
order by 2,1
Source: Stackoverflow.com