[JAVA] When performing a full outer join without using a full outer join
If you want to perform a full outer join, you can issue a query with "full outer join" as the keyword. Often not supported by some DB servers.
In such a case, use "union all" to perform a complete outer join. An example is shown in the query below.
select distinct * from (select e1.seirino,e1.time,e2.buyer＿no,e2.buyer＿name from daityou e1 inner join a0160 e2 on e1.seirino = e2.seirino where e1.time ='2018-10-27 00:00:00.0') j1 left join (select distinct a1.seirino,a1.buyer＿no,a1.a0162＿no,a1.A0162＿NAME,a1.a0164＿no ,a1.a0164＿name,a2.a0168＿no ,a2.a0168＿name
from (select distinct q1.seirino,q1.buyer＿no,q1.a0162＿no,q1.A0162＿NAME,q2.a0164＿no ,q2.a0164＿name from A0162 q1 left join A0164 q2 on q1.seirino=q2.seirino and q1.buyer＿no=q2.buyer＿no and q1.a0162＿no=q2.a0164＿no union all select q2.seirino,q2.buyer＿no,null as a0164＿no,null as A0164＿NAME,q2.a0164＿no ,q2.a0164＿name from A0164 q2 where not exists(select * from A0162 q1 where q2.seiriNO = q1.seiriNO and q1.buyer＿no=q2.buyer＿no and q1.a0162＿no=q2.a0164＿no ) )
a1 left join A0168 a2 on a1.seirino = a2.
seirino and a1.buyer＿no=a2.buyer＿no and (a1.a0162＿no=a2.a0168＿no or a1.a0164＿no=a2.a0168＿no)
union all
select d2.seirino,d2.buyer＿no,null as a0162＿no,null as A0162＿NAME,null as a0164＿no,null as A0164＿NAME,d2.a0168＿no ,d2.a0168＿name from A0168 d2 where not exists(select * from (select distinct c1.seirino,c1.buyer＿no,c1.a0162＿no,c1.A0162＿NAME,c2.a0164＿no ,c2.a0164＿name from A0162 c1 left join A0164 c2 on c1.seirino=c2.seirino and c1.buyer＿no=c2.buyer＿no and c1.a0162＿no=c2.a0164＿no union all select c2.seirino,c2.buyer＿no,null as a0164＿no,null as A0164＿NAME,c2.a0164＿no ,c2.a0164＿name from A0164 c2 where not exists (select * from A0162 c1 where c2.seiriNO = c1.seiriNO and c1.buyer＿no=c2.buyer＿no and c1.a0162＿no=c2.a0164＿no ) ) d1 where d2.seiriNO = d1.seiriNO and d1.buyer＿no=d2.buyer＿no and (d1.a0162＿no=d2.a0168＿no or d1.a0164＿no=d2.a0168＿no))) j2 on j1.seirino=j2.seirino and j1.buyer＿no=j2.buyer＿no
order by j1.seirino,j1.buyer＿no ,j2.a0162＿no ASC NULLS LAST,j2.a0164＿no ASC NULLS LAST,j2.a0168＿no ASC NULLS LAST;
◆ Table state
SEIRINO TIME BUYER＿NO BUYER＿NAME SEIRINO BUYER＿NO A0162＿NO A0162＿NAME A0164＿NO A0164＿NAME A0168＿NO A0168＿NAME
1 2018-10-27 00:00:00.0 1 A 1 1 1 N1 1 A4 1 1
1 2018-10-27 00:00:00.0 1 A 1 1 2 N2 2 B4 2 2
1 2018-10-27 00:00:00.0 1 A 1 1 3 N3 3 C4 3 3
1 2018-10-27 00:00:00.0 1 A 1 1 4 N4 4 4 4 4
1 2018-10-27 00:00:00.0 1 A 1 1 null null 5 5 5 5
1 2018-10-27 00:00:00.0 1 A 1 1 null null null null 6 6
1 2018-10-27 00:00:00.0 2 B 1 2 1 M1 1 D4 1 1
1 2018-10-27 00:00:00.0 2 B 1 2 2 M2 2 D4 null null
1 2018-10-27 00:00:00.0 3 C 1 3 1 L1 1 D4 null null
1 2018-10-27 00:00:00.0 4 D null null null null null null null null
that's all.
Recommended Posts