Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select * from V$VERSION;


BANNER
BANNER_FULL
BANNER_LEGACY
CON_ID


Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
0


create table table_users (user_id, user_name) as
    select rownum, column_value 
    from sys.odciVarchar2List ('Иван1','Иван2','ИванЗ','Иван4','Иван5')
5 rows affected

create table table_phone (user_id, user_phone, phone_main) as
    select 1, '+79999999999', 1 from dual union all
    select 1, '+78888888888', 0 from dual union all
    select 2, '+77777777777', 1 from dual union all
    select 2, '+76666666666', 0 from dual union all
    select 3, '+75555555555', 0 from dual union all
    select 4, '+74444444444', 1 from dual
6 rows affected

select u.user_id, u.user_name, p.user_phone
from table_users u
left join (
    select user_id, max (user_phone) keep (dense_rank first order by phone_main desc) user_phone
    from table_phone
    group by user_id) p on p.user_id=u.user_id


USER_ID
USER_NAME
USER_PHONE


1
Иван1
+79999999999

2
Иван2
+77777777777

3
ИванЗ
+75555555555

4
Иван4
+74444444444

5
Иван5
null


select u.user_id, u.user_name, p.user_phone
from table_users u
outer apply (
    select max (user_phone) keep (dense_rank first order by phone_main desc) user_phone
    from table_phone p
    where p.user_id=u.user_id) p


USER_ID
USER_NAME
USER_PHONE


1
Иван1
+79999999999

2
Иван2
+77777777777

3
ИванЗ
+75555555555

4
Иван4
+74444444444

5
Иван5
null

USER_ID	USER_NAME	USER_PHONE
1	Иван1	+79999999999
2	Иван2	+77777777777
3	ИванЗ	+75555555555
4	Иван4	+74444444444
5	Иван5	null