WITH incoming_foreigners AS (
    SELECT
        i.PORT_CODE,
        SUM(i.AMT) AS 入国者数
    FROM IMMIGRATION i
    WHERE i.GROUP_CODE = '120' AND i.KIND_CODE = '110'
    GROUP BY i.PORT_CODE
),
outgoing_foreigners AS (
    SELECT
        i.PORT_CODE,
        SUM(i.AMT) AS 出国者数
    FROM IMMIGRATION i
    WHERE i.GROUP_CODE = '120' AND i.KIND_CODE = '120'
    GROUP BY i.PORT_CODE
)
SELECT
    p.PORT_CODE AS 港コード,
    p.PORT_NAME AS 港名,
    inc.入国者数,
    outg.出国者数,
    (inc.入国者数 - outg.出国者数) AS 差分
FROM PORT p
JOIN incoming_foreigners inc ON p.PORT_CODE = inc.PORT_CODE
JOIN outgoing_foreigners outg ON p.PORT_CODE = outg.PORT_CODE
WHERE inc.入国者数 > outg.出国者数
ORDER BY 差分 DESC, 港コード DESC;