WITH log AS (
    select
        PORT_CODE,
        KIND_CODE,
        SUM(i.AMT) AS AMT
    from
        IMMIGRATION i
    join
        GRP g
    on
        i.GROUP_CODE = g.GROUP_CODE
    where
        g.GROUP_NAME = "外国人"
        AND i.KIND_CODE in (110, 120)
    group by
        PORT_CODE, KIND_CODE
),
target AS (
    SELECT
        p.PORT_CODE,
        inx.AMT AS in_amt,
        out.AMT AS out_amt
    FROM
        PORT p
    JOIN
        (SELECT * FROM log WHERE KIND_CODE=110) inx
    ON
        p.PORT_CODE = inx.PORT_CODE
    JOIN
        (SELECT * FROM log WHERE KIND_CODE=120) out
    ON
        p.PORT_CODE = out.PORT_CODE
    WHERE
        inx.AMT > out.AMT
)
SELECT
    t.PORT_CODE AS "港コード",
    p.PORT_NAME AS "港名",
    t.in_amt AS "入国者数",
    t.out_amt AS "出国者数",
    t.in_amt - t.out_amt AS "差分"
FROM
    target t
JOIN
    PORT p
ON
    t.PORT_CODE = p.PORT_CODE
ORDER BY
    "差分" desc,
    "港コード" desc