SELECT
	IMMIGRATION.PORT_CODE AS '港コード',
    PORT_NAME AS '港名',
    I AS '入国者数',
    O AS '出国者数',
    I - O AS '差分'
FROM IMMIGRATION
JOIN PORT
	ON IMMIGRATION.PORT_CODE = PORT.PORT_CODE
JOIN GRP
    ON IMMIGRATION.GROUP_CODE = GRP.GROUP_CODE
JOIN (SELECT PORT_CODE, AMT AS 'I' FROM IMMIGRATION WHERE KIND_CODE = 110) AS INBOUND
    ON IMMIGRATION.PORT_CODE = INBOUND.PORT_CODE
JOIN (SELECT PORT_CODE, AMT AS 'O' FROM IMMIGRATION WHERE KIND_CODE = 120) AS OUTBOUND
    ON IMMIGRATION.PORT_CODE = OUTBOUND.PORT_CODE
WHERE
    GROUP_NAME = '外国人'
    AND I - O >= 0
GROUP BY
    IMMIGRATION.PORT_CODE
ORDER BY
    I - O desc,
    IMMIGRATION.PORT_CODE desc