with ids(i) as (
  select level from dual connect by level <=5
),
props(n,t) as (
  select 1, 'one' from dual union all
  select 2, 'two' from dual 
)
select i, nvl(t,'unknown')
  from ids 
  left join props on i=n 

with ids(i) as (
  select level from dual connect by level <=5
),
props(n,t) as (
  select 1, 'one' from dual union all
  select 2, 'two' from dual union all
  select null, 'unlisted' from dual
)
select i, coalesce(t,(select t from props where n is null))
  from ids 
  left join props on i=n 

select i, p2.*
  from ids 
  left join props p1 on i=p1.n
  left join props p2 on decode(p1.n,p2.n,1) > 0

props(mask,text) as (
  select 'Adam', 'exact match' from dual union all
  select 'Le%',  'partial match' from dual union all
  select null,   'no other match' from dual
)

with ids(fname) as (
  select 'Adam'    from dual union all
  select 'Harry'   from dual union all
  select 'Leonard' from dual union all
  select 'Mary'    from dual union all
  select 'Lea'     from dual union all
  select 'James'   from dual
),
props(mask,text) as (
  select 'Adam', 'exact match'    from dual union all
  select 'Lea',  'exact match'    from dual union all
  select 'Le%',  'kinda match'    from dual union all
  select '%',    'no match'       from dual
)
select fname, max(mask) keep (dense_rank first order by text)
  from ids
  join props on fname like mask
 group by fname