Google представил новый язык программирования Logica

SQL — это язык для аналитики а не оптимизации

вот прям сейчас смотрю на проекте именно на реализацию с deleted users… и если честно, лучше б это было поле is_deleted.

Так с ограничениями SQL конечно лучше делать is_deleted. Но вот конкретные примеры недостатков было бы интересно узнать.

Можно делать и не так. Оптимально ли?

Так, как надо делать, в SQL сделать нельзя, пример далее.

Согласен, выглядит «стильно» — но зачем?

Затем же, зачем объявляются классы, выделяются функции, именуются переменные. Для семантики. В ORM например так и делают, джойн по названию связи, это выражает то, что мы хотим сделать.

Кто его знает что там за фк у вас. а если он составной
и его название выглядит как FK_scheme_table_field1_scheme_table_field2_on_scheme_table2_field1_scheme_table2_field2

Если он составной, в JOIN все равно будут эти поля.
Так не надо такие названия давать, название fk это название соответствующей связанной сущности.

А сокращенное название никак не даст представление о том, что там.

Нам не надо знать, что там, нам надо сказать движку, что мы хотим получить данные связанной сущности. Что там мы и так знаем, если работаем с этими таблицами не первый раз. А первый раз можно и посмотреть. Вы же не пишете в название функции всю ее реализацию printHelloPrintWorldPrintNewline().

Откуда привычка экономить буквы?

Тут цель не экономить буквы, а выразить то, что мы хотим выразить.
Но это было скорее о том, как надо было изначально сделать джойны, а если уж делать по-нормальному, то надо делать это более объектно-ориентировано.

SELECT id, title, text,
    author(id, nickname),
    comments(id, text, author(id, nickname) WHERE moderated = true)
FROM article
WHERE article.id = 123

Ну так мы же про язык запросов говорим. Дублирование данных при джойне это его недостаток. Потому что это делалось в те времена, когда плоские таблицы в результате было проще реализовать, чем иерархичные объекты, да и не совсем было понятно как вообще делать.

Условия джойна бывают не только по FK, не только по равенству и тд.

Поэтому я и написал "в 90% случаев".
Но вообще любой конкретный набор условий означает некую связь, которой можно дать название. Как блоку кода, который можно вынести в функцию.
А уж если и этого недостаточно, то для 1% случаев можно и специальный оператор предусмотреть. Это улучшит понимаемость кода — написан редкий оператор, значит тут что-то нестандартное, надо обратить внимание на это место. А то часто пропускаешь какой-нибудь дополнительный AND в условии ON, который начинается со стандартной связи по other_table_id = other_table.id.

Это решается довольно просто при помощи агрегатных функций

Каким образом с помощью агрегатных функций можно убрать дублирование строки таблицы articles в этом запросе?

SELECT articles.*, comments.*
FROM articles
INNER JOIN comments ON comments.article_id = articles.id
WHERE articles.id = 123

Смотря что здесь хочется оптимизировать.

Поддержку, то есть чтение и написание кода, понимание что там происходит, упрощение модификаций при изменении требований.