with 
def_codes(name, left_range, right_range) as
  (
    select 'Мегафон',   1, 100 from dual union all
    select 'Билайн' , 101, 300 from dual
  ),
def_mnp(name, phone) as 
  (
    select 'Теле2', 7 from dual union all
    select 'МТС',   8 from dual
  ),
all_users(phone) as
  (
    select 7    from dual union all
    select 8    from dual union all
    select 150  from dual
  )
select 
  an.phone,
  nvl(mnp.name, dc.name)
from       all_users  an
left join  def_mnp    mnp on an.phone = mnp.phone
left join  def_codes  dc  on an.phone between dc.left_range and dc.right_range and mnp.phone is null;

with 
def_codes(name, left_range, right_range) as
  (
    select 'Мегафон',   1, 100 from dual union all
    select 'Билайн' , 101, 300 from dual
  ),
def_mnp(name, phone) as 
  (
    select 'Теле2', 7 from dual union all
    select 'МТС',   8 from dual
  ),
all_users(phone) as
  (
    select 7    from dual union all
    select 8    from dual union all
    select 150  from dual
  )
select 
  an.phone,
  nvl(mnp.name, dc.name)
from       all_users  an
left join  def_mnp    mnp on an.phone = mnp.phone
left join  def_codes  dc  on an.phone between dc.left_range and dc.right_range /*and mnp.phone is null*/;