Skip to content
Advertisement

getting output of union all without using union all

I have been given a situation to find the common record between 2 tables without using union. I could do it. But I am not able to do ‘union all’. I mean I have to find out output of 2 table including duplicate without using union all. is there anyway to do it?

table A has x column and values 1,2,3 
     and
table B has x column and values 3,4,5

select x from A union select x from B;
o/p 1,2,3,4,5

select x from A union all select x from B;
o/p should be 1,2,3,3,4,5,6(not necessarily in order)

Union output I can achieve through below query

select nvl(a.x,b.x) output from A full outer join B on A.x=b.X order by output;

but I am not able to do union all without using oracle inbuilt union all.

Advertisement

Answer

You may get plus points in the interview if you mention some edge cases, here it could be:

Primary Key

If the column in one of the tables is not a primary key (or at leadt unique) the proposed solution for UNION

select nvl(a.x,b.x) output from A full outer join B on A.x=b.X

fails and you have to use DISTINCT

select distinct  nvl(a.x,b.x) output from A full outer join B on A.x=b.X 

Nullable Column

If the column in one of the tables is nullable the proposed solution for UNION ALL

select nvl(a.x, b.x) x from a full join b on a.x is null or b.x is null

fails. The other solution with on 1=2 works fine.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement