Коллеги, нужно сделать LEFT JOIN, отличный от тривиального ON (A.id

= B.id).

Для примера пусть будут две таблицы с одним числовым полем каждая.

A.id
1
2
3
4
5
6

B.id
1
3
5

Нужно составить SELECT A.id, B.id FROM A LEFT JOIN B ..., чтобы в результате получить:

1, 1
2, 1
3, 3
4, 3
5, 5
6, NULL

Думаю, понятно. Для каждого id из таблицы A нужно найти соответствие по максимальному id из таблицы B, который не больше id из таблицы A. В сторону чего смотреть?

В сторону понимания того, что любой join - это, по сути, некое подмножество полного произведения двух таблиц, выбранное по предикату ваша задача - составить предикат этого выбора

Ну, и какому условию ON соответствует данная выборка? A.id ? B.id

А почему 6,null? По условию задачи 6,5 должно быть

ON(B.id FROM B WHERE B.id <= A.id ORDER BY B.id DESC LIMIT 1)

Да, действительно. Хотелось подчеркнуть, что для каждого id из А нужно что-то вывести, даже если не найдётся соответствия.

Нет, это не условие. В этом и проблема, составить предикат.

Это учебная задача или нет?

select B.id, A.id from B join A on A.id >= B.id and A.id < coalesce(LEAD(B.id) over (order by B.id), INT_MAX) order by 1, 2 Что-то такое, гуглите LEAD/LAG Имхо, ни один движок SQL не умеет такое оптимизировать (идти по 2 индексам параллельно). Умеют только идти по 1 индексу и дёргать второй. Вариант идти по B дёргать A выгоднее, если записей в А сильно больше

оставлю и академический вариант select a.id as aid, b.id as bid from a left join b on b.id = (select max(t.id) from b as t where t.id <= a.id) order by 1;

Видимо ввожу в заблуждение отсутствием причины своего вопроса. Задача боевая, в наследство достался элементарный джоин, который выдаёт не то, что ожидается. Я представляю, как можно исправить не в один джоин, но вероятно это будет медленнее по производительности, чем один оптимизированый джоин.

Спасибо, пойду разбираться.

Спасибо, буду знать

Тогда что-то вроде: WITH a(id) AS ( VALUES (1), (2), (3), (4), (5), (6) ), b(id) AS ( VALUES (1), (3), (5) ) SELECT * FROM a LEFT JOIN LATERAL ( SELECT MAX(b.id) AS max_b_id FROM b WHERE b.id <= a.id ) AS bm ON true; будет лучше в большинстве ситуаций.

в кратком варианте да, но если в bm нужно ещё что-то тащить, то order by id desc limit 1

Да, конечно — был показан слишком простой пример. ;)

Спасибо, действительно быстро.