WITH ENTRANT AS (
    SELECT
        I.PORT_CODE,
        P.PORT_NAME,
        I.AMT AS '入国者数'
    FROM
        IMMIGRATION I
        INNER JOIN PORT P
            ON P.PORT_CODE = I.PORT_CODE
        INNER JOIN GRP G
            ON G.GROUP_CODE = I.GROUP_CODE
    WHERE
        I.KIND_CODE = 110
        AND G.GROUP_NAME = '外国人'
),
EXPARTIRIATE AS (
    SELECT
        I.PORT_CODE,
        P.PORT_NAME,
        I.AMT AS '出国者数'
    FROM
        IMMIGRATION I
        INNER JOIN PORT P
            ON P.PORT_CODE = I.PORT_CODE
        INNER JOIN GRP G
            ON G.GROUP_CODE = I.GROUP_CODE
    WHERE
        I.KIND_CODE = 120
        AND G.GROUP_NAME = '外国人'
)
SELECT
    E_1.PORT_CODE AS '港コード',
    E_1.PORT_NAME AS '港名',
    入国者数,
    出国者数,
    入国者数 - 出国者数 AS '差分'
FROM
    ENTRANT E_1
    INNER JOIN EXPARTIRIATE E_2
        ON E_2.PORT_CODE = E_1.PORT_CODE
WHERE
    入国者数 >= 出国者数
ORDER BY
    差分 DESC,
    E_1.PORT_CODE DESC
;