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.