-- IMMIGRATIONはin,out両方とも常にある前提
SELECT
ina.PORT_CODE AS 港コード,
p.PORT_NAME AS 港名,
ina.amt AS 入国者数,
out.amt AS 出国者数,
ina.amt - out.amt AS 差分
FROM IMMIGRATION ina
INNER JOIN IMMIGRATION out
ON out.PORT_CODE = ina.PORT_CODE
AND out.KIND_CODE = 120 -- 出国
AND out.GROUP_CODE = 120 -- 外国人
INNER JOIN PORT p
ON p.PORT_CODE = ina.PORT_CODE
WHERE ina.KIND_CODE = 110 -- 入国
AND ina.GROUP_CODE = 120 -- 外国人
AND ina.amt > out.amt
ORDER BY 差分 DESC , ina.PORT_CODE DESC