LEFT JOIN ... ON ... написать после ON составное условие с использованием столбца таблицы справа, не являющимся FK на таблицу слева.
Этот ебучий случай называется left outer join with additional condition.
У меня столько багов на эти кондишены было!
Кроме того, открыл для себя конструкцию ORDER BY ... DESC NULLS LAST
У нас в проекте про них знает... Гм... Четыре разработчика (индексы, сортировка, физплан, лплан) и пара тестеров =))))

Этот ебучий случай называется left outer join with additional condition.
У меня столько багов на эти кондишены было!

Ну вот я тоже слегка удивился, когда написал без обращения к документации, а оно возьми да и заработай. Понятно, что теоретически сложного ничего нет, ну а практически - тоже понятно, что это геморрой для разработчиков СУБД тот еще. Вот не знаю, тот же MySQL умеет ли вообще дополнительные условия?

У нас в проекте про них знает... Гм... Четыре разработчика (индексы, сортировка, физплан, лплан) и пара тестеров =))))

Я, кстати, ради интереса посмотрел, как подобное написать в MySQL - тоже можно, но подход несколько другой, там будет два условия сортировки, одно из который - по результату применения выражения isnull.

Ну вот я тоже слегка удивился, когда написал без обращения к документации, а оно возьми да и заработай. Понятно, что теоретически сложного ничего нет, ну а практически - тоже понятно, что это геморрой для разработчиков СУБД тот еще. Вот не знаю, тот же MySQL умеет ли вообще дополнительные условия?
Это стандарт SQL92,
А гемморой очень простой - дополнительные условия в left join можно выполнить только внутри джойна. Его не вытащишь наружу. А причина банальная - условие работает лишь для ГРУППЫ значений справа, взятого для ДАННОЙ ключа слева. Если всё фильтранул - NULL'ы, иначе - запись.
Никаких условием сверху-снизу не вставишь.
Но это при условии , что в этом кондишене участвуют колонки слева-справа.

А если участвуют лишь колонки справа, то по идее оптимизатор условие может под правый вход спустить *чешет репу, соображает*
А если только колонки слева - то не сможет спустить.

это очень странно

Но что конкретно?

ну про колонки, не являющиеся FK

Ну у меня там, условно говоря, из статистической серии по временным периодам нужно выбрать данные по конкретному периоду, но этих данных может вообще не быть, тогда надо вытащить NULL'ы. Вот условие на период - это и есть вторая часть условия в ON, которая не на FK.
Я вроде бы отдаю себе отчет в том, что это, наверное, просадит меня по производительности, но это ж веб, веб все стерпит.
Там этих периодов не такое количество, да и требования по времени исполнения запроса довольно либеральны.

По хорошему-то надо бы периоды забивать в отдельную таблицу тоже, тогда будет как бы не один джойн, а два, но на это просто не хватило сил.
Пусть СУБД работает, че все я-то?

Да, left outer join + additional condition конкретно так могут подсадить запрос, но лишь в НЕКОТОРЫХ случаях.
loj + ac налагает более жёсткие требования на join-алгоритм и входы, ломает многопроходную схему.
НО - это же MySQL? В нём все nested-loop'ами делается, так что он всё стерпит.

У меня постгря.
Кстати, условие дополнительное только на правую таблицу, так что фильтрануть можно.
Да можно было бы и нормализовать по-человечески, но лень раньше меня родилась.

Не, ну тогда всё чихпых - это условие просто "провалиться" под правый вход, и у нас будет чистый left outer join.
ЕСЛИ это не так, то дать по жопе разработчикам.
Я потом пошуршу сорцы, на предмет того так оно или нет.

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

после ON составное условие с использованием столбца таблицы справа, не являющимся FK на таблицу слева.

Недавно открыл для себя оракловый CONNECT BY, так там фильтрацию дерева именно что приходилось добавлять в JOIN, а если JOINа не было, то дешевле было приделывать JOIN, чем фильтровать через WHERE. Так что иногда очень даже имеет смысл, да.