WITH 入国者 AS (
	SELECT
		PORT_CODE
		, GROUP_CODE
		, KIND_CODE
		, AMT
	FROM
		IMMIGRATION
	WHERE
		KIND_CODE = '110'
		AND GROUP_CODE = '120'
), 出国者 AS (
	SELECT
		PORT_CODE
		, GROUP_CODE
		, KIND_CODE
		, AMT
	FROM
		IMMIGRATION
	WHERE
		KIND_CODE = '120'
		AND GROUP_CODE = '120'
)
SELECT
	A.PORT_CODE AS 港コード
	, PORT.PORT_NAME AS 港名
	, A.AMT AS 入国者数
	, (SELECT AMT FROM 出国者 B WHERE A.PORT_CODE = B.PORT_CODE AND A.GROUP_CODE = B.GROUP_CODE) AS 出国者数
	, A.AMT - (SELECT AMT FROM 出国者 B WHERE A.PORT_CODE = B.PORT_CODE AND A.GROUP_CODE = B.GROUP_CODE) AS 差分
FROM
	入国者 A
	INNER JOIN
		PORT
		ON A.PORT_CODE = PORT.PORT_CODE
WHERE
	EXISTS(
		SELECT
			*
		FROM
			出国者 B
		WHERE
			A.PORT_CODE = B.PORT_CODE
			AND A.GROUP_CODE = B.GROUP_CODE
			AND B.AMT < A.AMT
	)
ORDER BY
	差分 DESC
	, 港コード DESC