SELECT
a.PORT_CODE as 港コード,
a.PORT_NAME as 港名,
b.AMT as 入国者数,
c.AMT as 出国者数,
b.AMT-c.AMT as 差分
FROM
PORT a
JOIN
(SELECT
ins.PORT_CODE,
ins.AMT
FROM
IMMIGRATION ins
JOIN
GRP ig
ON
ins.GROUP_CODE = ig.GROUP_CODE
WHERE
ig.GROUP_NAME ='外国人' and
ins.KIND_CODE = '110')  b
ON
a.PORT_CODE = b.PORT_CODE
JOIN
(SELECT
ous.PORT_CODE,
ous.AMT
FROM
IMMIGRATION ous
JOIN
GRP og
ON
ous.GROUP_CODE = og.GROUP_CODE
WHERE
og.GROUP_NAME ='外国人' and
ous.KIND_CODE = '120')  c
ON
a.PORT_CODE = c.PORT_CODE
WHERE
b.AMT > c.AMT
ORDER BY
差分 DESC,
a.PORT_CODE DESC