WITH foreign_in AS(
    
    SELECT PORT_CODE, GROUP_CODE, KIND_CODE, AMT AS 入国者数
    FROM IMMIGRATION
    WHERE KIND_CODE = "110" AND GROUP_CODE = "120"
    
), foreign_out AS(

    SELECT PORT_CODE, GROUP_CODE, KIND_CODE, AMT AS 出国者数
    FROM IMMIGRATION
    WHERE KIND_CODE = "120" AND GROUP_CODE = "120"    
)

SELECT i.PORT_CODE AS 港コード, p.PORT_NAME AS 港名, i.入国者数, o.出国者数, (i.入国者数 - o.出国者数) AS 差分
FROM foreign_in i
INNER JOIN PORT p
ON i.PORT_CODE = p.PORT_CODE
INNER JOIN foreign_out o
ON i.PORT_CODE = o.PORT_CODE

WHERE i.入国者数 > o.出国者数

ORDER BY 差分 DESC, 港コード DESC;