Как выбрать только одну строку из таблицы с внешним соединением?

Question

Есть две таблицы.

Пользователи:

USER_ID	USER_NAME
1	Иван1
2	Иван2
3	ИванЗ
4	Иван4
5	Иван5

Телефоны:

USER_ID	USER_PHONE	PHONE_MAIN
1	+79999999999	1
1	+78888888888	0
2	+77777777777	1
2	+76666666666	0
3	+75555555555	0
4	+74444444444	1

Необходимо объединить эти две таблицы в одну, но при этом телефон должен подвязаться только один раз, с условием, что если если есть основной (phone_main=1), то присоединить эту запись, если основного нет, то присоединить не основной (phone_main=0)

Как можно сделать оптимизированный запрос, чтобы итоговая таблица получилась такой:

USER_ID	USER_NAME	USER_PHONE
1	Иван1	+79999999999
2	Иван2	+77777777777
3	ИванЗ	+75555555555
4	Иван4	+74444444444
5	Иван5	пусто

Есть ли возможность в join сделать проверку?

select tu.user_id, tu.user_name, tp.user_phone
from table_user tu
left join table_phone tp on tp.user_id=tu.user_id and (tp.phone_main = 1 or tp.phone_main = 0)

Вышеуказанный код возвращает две строки, т.к. or учитывает и то и другое.

Пока только такое решение нашел:

select tu.user_id, tu.user_name, coalesce(tp1.user_phone, tp2.user_phone)
from table_user tu
left join table_phone tp1 on tp1.user_id=tu.user_id and tp1.phone_main = 1
left join table_phone tp2 on tp2.user_id=tu.user_id and tp2.phone_main = 0

Answer 1

Используйте коррелированный подзапрос:

SELECT tu.user_id, 
       tu.user_name, 
       ( SELECT tp.user_phone
         FROM table_phone tp
         WHERE tp.user_id=tu.user_id
         ORDER BY phone_main DESC LIMIT 1 ) user_phone
FROM table_user tu

А для PL/SQL используйте оконную функцию. Например:

WITH cte AS (
SELECT tu.user_id, 
       tu.user_name, 
       tp.user_phone, 
       ROW_NUMBER() OVER (PARTITION BY tp.user_id ORDER BY tp.phone_main DESC) rn
FROM table_user tu
LEFT JOIN table_phone tp ON tp.user_id=tu.user_id
)
SELECT * FROM cte WHERE rn = 1

Answer 2

при этом телефон должен подвязаться только один раз

То есть задача, взять из связаной таблицы только первую подходящую запись.

Решение со стандартным соединением:

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
/

Или более эффективно с коррелированным соединением таблиц:

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
/

Оба запроса вернут ожидаемый результат (на db<>fiddle):

  USER_ID USER_NAME    USER_PHONE  
---------- ------------ ------------
         1 Иван1        +79999999999
         2 Иван2        +77777777777
         3 ИванЗ        +75555555555
         4 Иван4        +74444444444
         5 Иван5        пусто

БЛОГ НА HUSL

Как выбрать только одну строку из таблицы с внешним соединением?

Ответы (2 шт):