with foreign_in as (
    select
        PORT_CODE,
        AMT
    from
        IMMIGRATION
    where
        GROUP_CODE = '120'  -- 外国人
        and
        KIND_CODE = '110'   -- 入国者
), foreign_out as (
    select
        PORT_CODE,
        AMT
    from
        IMMIGRATION
    where
        GROUP_CODE = '120'  -- 外国人
        and
        KIND_CODE = '120'   -- 出国者
) select
    distinct IMMIGRATION.PORT_CODE as 港コード,
    PORT.PORT_NAME 港名,
    foreign_in.AMT 入国者数,
    foreign_out.AMT 出国者数,
    foreign_in.AMT - foreign_out.AMT as 差分
from IMMIGRATION
    join PORT on PORT.PORT_CODE = IMMIGRATION.PORT_CODE
    join foreign_in on foreign_in.PORT_CODE = IMMIGRATION.PORT_CODE
    join foreign_out on foreign_out.PORT_CODE = IMMIGRATION.PORT_CODE
where
    foreign_in.AMT > foreign_out.AMT
order by 差分 desc, IMMIGRATION.PORT_CODE desc
;