CREATE TEMPORARY TABLE SUM_TATE_POP AS
    SELECT
        IMMIGRATION.PORT_CODE
        ,IMMIGRATION.KIND_CODE
        ,SUM(IMMIGRATION.AMT) AS POP
    FROM
        IMMIGRATION
    WHERE GROUP_CODE = 120
    GROUP BY IMMIGRATION.PORT_CODE, IMMIGRATION.KIND_CODE
    HAVING IMMIGRATION.KIND_CODE IN ("110", "120")
;

-- SELECT * FROM SUM_POP

CREATE TEMPORARY TABLE SUM_YOKO_POP AS
    SELECT
        SUM_TATE_POP.PORT_CODE
        ,MAX(CASE WHEN SUM_TATE_POP.KIND_CODE = "110" THEN SUM_TATE_POP.POP END) AS "入国者数"
        ,MAX(CASE WHEN SUM_TATE_POP.KIND_CODE = "120" THEN SUM_TATE_POP.POP END) AS "出国者数"
    FROM
        SUM_TATE_POP
    GROUP BY SUM_TATE_POP.PORT_CODE
;

-- SELECT * FROM SUM_YOKO_POP


SELECT
    PORT.PORT_CODE AS "港コード"
    ,PORT.PORT_NAME AS "港名"
    ,入国者数 AS "入国者数"
    ,出国者数 AS "出国者数"
    ,入国者数 - 出国者数 AS "差分"
FROM
    PORT
LEFT JOIN SUM_YOKO_POP ON PORT.PORT_CODE = SUM_YOKO_POP.PORT_CODE
WHERE 入国者数 - 出国者数 > 0
ORDER BY 差分 DESC, PORT.PORT_CODE DESC