WITH IMMIGRATION_IN AS (
    SELECT
        I.PORT_CODE,
        I.AMT AS 入国者数
    FROM
        IMMIGRATION I
        INNER JOIN GRP G
            ON G.GROUP_CODE = I.GROUP_CODE
    WHERE
        G.GROUP_NAME = '外国人'
        AND I.KIND_CODE = '110'
),

IMMIGRATION_OUT AS (
    SELECT
        I.PORT_CODE,
        I.AMT AS 出国者数
    FROM
        IMMIGRATION I
        INNER JOIN GRP G
            ON G.GROUP_CODE = I.GROUP_CODE
    WHERE
        G.GROUP_NAME = '外国人'
        AND I.KIND_CODE = '120'
)

SELECT
    P.PORT_CODE AS 港コード,
    P.PORT_NAME AS 港名,
    I_I.入国者数,
    I_O.出国者数,
    I_I.入国者数 - I_O.出国者数 AS 差分
FROM
    IMMIGRATION_IN I_I
    INNER JOIN IMMIGRATION_OUT I_O
        ON I_I.PORT_CODE = I_O.PORT_CODE
    INNER JOIN PORT P
        ON P.PORT_CODE = I_I.PORT_CODE
ORDER BY
    差分 DESC,
    港コード DESC
;