with imf as (
select i.AMT,i.PORT_CODE,i.KIND_CODE
from IMMIGRATION i
join GRP g
on i.GROUP_CODE = g.GROUP_CODE
where g.GROUP_NAME = '外国人'
)


select p.PORT_CODE as 港コード,
p.PORT_NAME as 港名,
i.AMT as 入国者数,
o.AMT as 出国者数,
i.AMT - o.AMT as 差分
from 
PORT p
join 
    (select * 
    from imf 
    where KIND_CODE = '110') as i
on i.PORT_CODE = p.PORT_CODE     
join    
    (select * 
    from imf 
    where KIND_CODE = '120') as o
on o.PORT_CODE = p.PORT_CODE 
where i.amt > o.amt
order by i.amt - o.amt desc,p.PORT_CODE desc