Join table on one of the row I have a table, say, Person with fields

id  name
1    Jone
2    Jane


Got another table address

id   person_id   address_type
1    1           Permanent
2    1           Temporary


I want to select table Person to Address such that if a person has Temporary address then join it else join with Parmanent address.

I can do:

    select * 
    from person p 
    left join address at on at.person_id=p.id and at.type='Temporary' 
    left join address al on al.person_id=p.id and at.type='Permanent'


But joining address table twice seems like overkill here. Can I just join address only once such that I get:

-&gt; If Temporary is there, join with Temporary
-&gt; If temporary is not there, join with permanent
-&gt; If pernamenet is not there join address with all null fields.


Help?
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)