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'ы, иначе - запись.
Никаких условием сверху-снизу не вставишь.
Но это при условии , что в этом кондишене участвуют колонки слева-справа.

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