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.