For each циклы с LATERAL соединениями

При объединении нескольких таблиц строки обеих таблиц связываются между собой на основе некоторых условий. Однако если результат должен быть ограничен, например, тремя последними купленными товарами для каждого покупателя, стандартное предложение соединение не будет работать: Объединённая таблица купленных товаров не может быть ограничена только тремя строками для каждого покупателя. Так не работает объединение, в него включаются все строки, соответствующие критериям объединения. Но при латеральном присоединении вместо таблицы будет присоединён подзапрос, который выполняется один раз для каждого покупателя, что приводит к эквивалентному циклу for-each в SQL.

Использование

MySQL / PostgreSQL

SELECT customers.*, recent_sales.*
FROM customers
LEFT JOIN LATERAL (
    SELECT *
    FROM sales
    WHERE sales.customer_id = customers.customer_id
    ORDER BY created_at DESC
    LIMIT 3
) AS recent_sales ON true;

Подробное объяснение

Обычно соединение выполняется константным образом: Образно говоря, каждая строка одной таблицы сопоставляется с каждой строкой другой таблицы. Только если условие соединения справедливо для комбинации обеих строк, они включаются в результирующий набор. Такой алгоритм называется вложенным циклом (nested-loop join) и является наиболее простым для понимания. Однако в некоторых ситуациях при не константном соединении требуется не ограничивать весь результат, а LIMIT строки объединённой таблицы для каждой строки исходной таблицы. Но это требование невозможно при использовании константного соединения, поскольку оно нарушает реляционную алгебру, заключающуюся в простом соединении двух наборов данных с помощью некоторых операторов.

Соединение таблицы с подзапросом также не поможет решить поставленную задачу. Любой соединённый подзапрос выполняется как независимый подзапрос; он выполняется один раз для всей операции соединения. База данных просто преобразует подзапрос в структуру, подобную таблице:

• Сначала выполняется подзапрос.
• Результаты временно сохраняются в памяти или на диске (создаётся "виртуальная таблица").
• Алгоритм объединения выполняется с исходной таблицей и виртуальной целевой таблицей.

Существенным отличием и замечательной особенностью LATERAL соединения является изменение модели выполнения: Вместо того чтобы выполнять подзапрос один раз для всех строк, теперь он выполняется один раз для каждой строки, к которой присоединяется подзапрос. Процедура имитирует цикл for-each в SQL, который выполняет итерацию по исходной таблице и выполняет литеральное соединение для каждой строки, при этом строка исходной таблицы является входной. Раньше было невозможно запросить три последних купленных продукта для каждого покупателя, но с помощью литерального соединения, подобного циклу for-each, это стало проще.

При латеральном соединении выполнение подзапроса в соединение меняется с независимого подзапроса на зависимый/производный подзапрос. Выполнение запроса для каждой строки по одному разу может иметь определённые последствия для производительности. Однако эти последствия не являются причиной для отказа от их использования. Без латеральных соединений приложение реализует цикл for-each в программном коде: Подзапрос посылается в базу данных несколько раз, и его нужно многократно разбирать, планировать и выполнять. При использовании латерального соединения база данных может выбрать более оптимальный план выполнения и выполнить этап разбора и планирования только один раз. Таким образом, нельзя сказать, что латеральное соединение — это медленная функция базы данных, которой следует избегать. Напротив, это мощная функция, переносящая цикл for-each приложения в базу данных, которая может выполнять его гораздо эффективнее.

Дополнительные ресурсы

• MySQL Documentation: Lateral Derived Tables as a specific form of subqueries.
• PostgreSQL documentation: LATERAL Subqueries as one of multiple table expression constructs.
• StackOverflow Blog: What is the difference between a LATERAL JOIN and a subquery?