full outer join を使わずに完全外部結合を行う場合

完全外部結合を行う場合、「full outer join」をキーワードにクエリを発行すればよいが、
DBサーバによってはサポートされていないことが多い。

そういった場合は「union all」を使って完全外部結合を行う。
以下クエリでその例を示す。

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;

◆テーブル状態

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

以上。