with immigrants as(
    select
    	PORT_CODE,
    	KIND_CODE,
        AMT
    from
    	IMMIGRATION as i
    	inner join
    		GRP as g
    		on i.GROUP_CODE = g.GROUP_CODE
    where
    	KIND_CODE = '110'
    	and
    	GROUP_NAME = '外国人'
),departure as(
    select
    	PORT_CODE,
    	KIND_CODE,
        AMT
    from
    	IMMIGRATION as i
    	inner join
    		GRP as g
    		on i.GROUP_CODE = g.GROUP_CODE
    where
    	KIND_CODE = '120'
    	and
    	GROUP_NAME = '外国人'
), source as(
    select 
        distinct PORT_CODE,
        (select immigrants.AMT from immigrants
            where i.PORT_CODE = immigrants.PORT_CODE) as 'immigrants',
        (select departure.AMT from departure
            where i.PORT_CODE = departure.PORT_CODE) as 'departure'
    from 
        IMMIGRATION as i
    where
        immigrants > departure
)
select
    s.PORT_CODE as '港コード',
    PORT_NAME as '港名',
    immigrants as '入国者数',
    departure as '出国者数',
    (immigrants - departure) as '差分'
from
     source as s
    inner join
        PORT as p
        on s.PORT_CODE = p.PORT_CODE
order by
    差分 desc,
    s.PORT_CODE desc
;