Join table on one of the row
This could be done using a left join to a derived table that picks the correct address:

select p.*, a.id as address_id, a.address_type
from person p
  left join (
     select ad.*, 
            row_number() over (partition by ad.person_id 
                               order by case when ad.address_type = 'Temporary' then 1 else 2 end) as rn
     from address ad
  ) a on a.person_id = p.id and a.rn = 1;


The case expression for the order by inside the window function could also be written as decode(ad.address_type, 'Permanent', 1, 2), but I prefer standard SQL over Oracle specific one if both are equivalent.



Another option - that might or might not be faster - is to use a lateral join with a fetch first clause.

select p.*, a.id as address_id, a.address_type
from person p
  left join lateral (
     select *
     from address ad
     where ad.person_id = p.id
     order by case when ad.address_type = 'Temporary' then 1 else 2 end
     fetch first 1 rows only
  ) a on 1 = 1;


The lateral left join returns one row for each person_id from the outer query (because of the fetch first 1 rows only)