with i as(
select 
    port_code, 
    max(case when group_code = '120' and kind_code = '110' then amt else 0 end) as foreign_in, 
    max(case when group_code = '120' and kind_code = '120' then amt else 0 end) as foreign_out 
from
    immigration
group by
    port_code
)
select 
    i.port_code as "港コード",
    p.port_name as "港名",
    i.foreign_in as "入国者数",
    i.foreign_out as "出国者数",
    i.foreign_in - i.foreign_out as "差分"
from 
    i
    join port p on i.port_code = p.port_code
order by 
    i.foreign_in - i.foreign_out desc,
    i.port_code desc