VISHMAY VISHMAY - 1 month ago 9
SQL Question

can we replace full join with union of left and right join? why not?

can we replace full join with union of left and right join? if no,why?

Answer

'YES' if T1 and T2 are sets (no duplicated rows), otherwise the answer is 'NO'.

create table t1 (i int);
create table t2 (i int);

insert into t1 values (1);
insert into t1 values (2);
insert into t1 values (2);

insert into t2 values (3);

FULL JOIN

select * from t1 full join t2 on t1.i=t2.i 
order by 1,2

1   (null)
2   2
2   2
(null)  3

UNION

select * from t1 left join  t2 on t1.i=t2.i
union
select * from t1 right join t2 on t1.i=t2.i
order by 1,2

1   (null)
2   2
(null)  3  

UNION ALL

select * from t1 left join  t2 on t1.i=t2.i
union all
select * from t1 right join t2 on t1.i=t2.i
order by 1,2

1   (null)
2   2
2   2
2   2
2   2
(null)  3