WITH immigrater AS(
    SELECT
        PORT_CODE,
        PORT_NAME,
        KIND_CODE,
        SUM(AMT) AS 人数
FROM 
    IMMIGRATION
INNER JOIN 
    PORT USING(PORT_CODE)
INNER JOIN 
    GRP USING(GROUP_CODE)
WHERE
    GROUP_CODE = '120'
    AND KIND_CODE = '110'
GROUP BY
    PORT_CODE,
    PORT_NAME,
    KIND_CODE
), 

emigrater AS(
    SELECT
        PORT_CODE,
        PORT_NAME,
        KIND_CODE,
        SUM(AMT) AS 人数
    FROM 
        IMMIGRATION
    INNER JOIN 
        PORT USING(PORT_CODE)
    INNER JOIN 
        GRP USING(GROUP_CODE)
    WHERE
        GROUP_CODE = '120'
        AND KIND_CODE = '120'
    GROUP BY
        PORT_CODE,
        PORT_NAME,
        KIND_CODE
)

SELECT
    PORT_CODE AS 港コード,
    PORT_NAME AS 港名,
    i.人数 AS 入国者数,
    e.人数 AS 出国者数,
    i.人数 - e.人数 AS 差分
FROM 
    immigrater as i
INNER JOIN
    emigrater as e USING(PORT_CODE, PORT_NAME)
WHERE
    差分 > 0
ORDER BY
    差分 DESC,
    PORT_CODE DESC