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.![無題.png](https://qiita-image-store.s3.amazonaws.com/0/315106/a124ea02-6c31-20ee-1f24-63388d902faa.png)
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

以上。