WITH foreigner_entrant AS (
    SELECT
        PORT_CODE
        , AMT AS entrant_amt
    FROM
        IMMIGRATION
    WHERE
        GROUP_CODE = 120    -- 外国人
        AND KIND_CODE = 110 -- 入国者
)
, foreigner_departures AS (
    SELECT
        PORT_CODE
        , AMT AS departures_amt
    FROM
        IMMIGRATION
    WHERE
        GROUP_CODE = 120    -- 外国人
        AND KIND_CODE = 120 -- 出国者
)
SELECT
    foreigner_entrant.PORT_CODE AS '港コード'
    , PORT.PORT_NAME AS '港名'
    , foreigner_entrant.entrant_amt AS '入国者数'
    , foreigner_departures.departures_amt AS '出国者数'
    , (foreigner_entrant.entrant_amt - foreigner_departures.departures_amt) AS '差分'
FROM
    foreigner_entrant
INNER JOIN
    foreigner_departures ON foreigner_entrant.PORT_CODE = foreigner_departures.PORT_CODE
INNER JOIN
    PORT ON foreigner_entrant.PORT_CODE = PORT.PORT_CODE
ORDER BY
    (foreigner_entrant.entrant_amt - foreigner_departures.departures_amt) DESC
    , foreigner_entrant.PORT_CODE DESC
;