--入国者数
WITH j_sum AS (
SELECT
 PORT_CODE,
 PORT_NAME,
 SUM(AMT) AS J_AMT
FROM
 IMMIGRATION
LEFT JOIN
 PORT
USING
 (PORT_CODE)
WHERE
 KIND_CODE = '110'
GROUP  BY
 PORT_CODE,PORT_NAME
),

--出国者数
f_sum AS (
SELECT
 PORT_CODE,
 PORT_NAME,
 SUM(AMT) AS F_AMT
FROM
 IMMIGRATION
LEFT JOIN
 PORT
USING
 (PORT_CODE)
WHERE
 KIND_CODE = '120'
GROUP  BY
 PORT_CODE,PORT_NAME
)

--2つのテーブルをJOINする

SELECT
 j_sum.PORT_CODE AS '港コード',
 j_sum.PORT_NAME AS '港名',
 j_sum.J_AMT AS '入国者数',
 f_SUM.F_AMT AS '出国者数',
 j_sum.J_AMT - f_SUM.F_AMT AS '差分'
FROM
 j_sum
INNER JOIN
 f_sum
ON
 j_sum.PORT_CODE = f_sum.PORT_CODE
AND
 j_sum.J_AMT > f_sum.F_AMT
ORDER BY
 j_sum.J_AMT - f_SUM.F_AMT  DESC,j_sum.PORT_CODE  DESC