ASOF JOIN, LATERAL, POSITIONAL JOIN и BY NAME

Обычный JOIN соединяет строки по равенству ключей: строка слева встречается со строкой справа, если их ключи совпали. Этого достаточно для большинства задач, но не для всех. Что делать, когда точного совпадения нет в принципе — например, нужно сопоставить сделку с курсом валюты, который был актуален на момент сделки, а курсы записаны не в каждую миллисекунду? Что делать, когда соединять надо по позиции строки, а не по значению? Что делать, когда две таблицы имеют одинаковые колонки, но в разном порядке?

DuckDB добавляет к стандартным джойнам четыре специализированных инструмента. ASOF JOIN соединяет по ближайшему ключу вместо точного — это рабочая лошадка анализа временных рядов. LATERAL разрешает правой стороне джойна ссылаться на левую. POSITIONAL JOIN соединяет по номеру строки. UNION BY NAME и INSERT ... BY NAME сопоставляют колонки по именам, а не по позиции. Разберём каждый и поймём, какую задачу он закрывает.

ASOF JOIN: соединение по ближайшему ключу

Временные ряды редко синхронизированы. Сделки происходят в произвольные моменты, а котировки валюты обновляются по своему графику.

LATERAL: подзапрос в FROM, который видит соседей Чтобы для каждой сделки взять курс, актуальный на её момент, нужен джойн по правилу «возьми последнюю котировку, чьё время не позже времени сделки». Это не равенство — это «ближайший снизу».

Стандартный JOIN так не умеет. Классическое решение — коррелированный подзапрос с ORDER BY ... LIMIT 1 для каждой строки, что медленно и громоздко. ASOF JOIN делает это одной конструкцией:

-- trades:  trade_time, symbol, qty
-- quotes:  quote_time, symbol, price
-- Для каждой сделки взять последнюю котировку на её момент

SELECT
    t.trade_time, t.symbol, t.qty,
    q.price
FROM trades t
ASOF JOIN quotes q
  ON t.symbol = q.symbol
  AND t.trade_time >= q.quote_time;

Условие ASOF JOIN устроено особым образом. Часть с равенством (t.symbol = q.symbol) — обычные ключи. Часть с неравенством (t.trade_time >= q.quote_time) — это «asof-условие»: среди всех котировок того же символа с временем не позже времени сделки движок берёт ровно одну — с наибольшим quote_time. То есть ближайшую снизу.

Сравним поведение ASOF JOIN с обычным JOIN:

По умолчанию ASOF JOIN — внутренний (INNER): сделка без единой подходящей котировки выпадает из результата. Если котировки могло не быть вовсе, но сделку терять нельзя, пишут ASOF LEFT JOIN — тогда price будет NULL. Использовать ASOF JOIN стоит для любых данных «как было на момент»: курсы валют, цены, версии конфигурации, состояние сущности на дату.

LATERAL: правая сторона видит левую

В обычном JOIN правая таблица не знает о левой — обе стороны вычисляются независимо, потом соединяются. Иногда это мешает: нужно, чтобы подзапрос справа использовал значение из текущей строки слева. Например, для каждого пользователя достать три его последних заказа.

LATERAL снимает изоляцию: подзапрос справа от LATERAL может ссылаться на колонки таблицы слева. Он вычисляется заново для каждой строки левой таблицы, видя её значения.

-- Для каждого пользователя — его 3 последних заказа
SELECT u.name, recent.order_id, recent.order_date
FROM users u,
LATERAL (
    SELECT order_id, order_date
    FROM orders o
    WHERE o.user_id = u.user_id   -- ссылка на левую таблицу!
    ORDER BY order_date DESC
    LIMIT 3
) recent;

Ключевая строка — WHERE o.user_id = u.user_id. Без LATERAL подзапрос не имел бы права упомянуть u.user_id: для него u — внешняя таблица, недоступная. LATERAL разрешает эту ссылку. Логически подзапрос исполняется для каждой строки users отдельно, с подставленным значением u.user_id.

LATERAL решает класс задач «top-N внутри каждой группы», который иначе требует оконных функций с QUALIFY. Оба подхода валидны: LATERAL нагляднее, когда «правую часть» удобно описать как самостоятельный запрос; QUALIFY с row_number() часто эффективнее, потому что не требует перезапуска подзапроса на каждую строку.

POSITIONAL JOIN: соединение по номеру строки

Все джойны выше соединяют по значениям. POSITIONAL JOIN соединяет по позиции: первая строка левой таблицы встаёт рядом с первой строкой правой, вторая со второй, и так далее. Никакого ON — соответствие задаётся порядком строк.

-- Две таблицы, у которых строки соответствуют по порядку
-- measurements: одна колонка value
-- timestamps:   одна колонка ts

SELECT t.ts, m.value
FROM measurements m
POSITIONAL JOIN timestamps t;

Это нишевый, но незаменимый инструмент. Он нужен, когда два набора данных пришли как параллельные колонки без общего ключа — типично при разборе сырых файлов, где значение и его метаданные лежат в отдельных списках, соответствующих по индексу. Если одна таблица длиннее другой, недостающие строки короткой стороны заполняются NULL — поведение как у FULL OUTER JOIN, но по позиции.

UNION BY NAME и INSERT … BY NAME

Последняя пара инструментов — про сопоставление колонок по именам вместо позиции. Стандартный UNION соединяет результаты по позиции колонок: первая колонка с первой, вторая со второй. Имена игнорируются. Если у двух таблиц одни и те же колонки, но в разном порядке, обычный UNION молча склеит несовпадающие колонки и выдаст бессмыслицу.

UNION BY NAME сопоставляет колонки по именам:

-- q1: region, revenue
-- q2: revenue, region   (тот же набор, другой порядок)

-- Обычный UNION склеил бы region с revenue — мусор
-- UNION ALL BY NAME сопоставит по именам
SELECT region, revenue FROM q1
UNION ALL BY NAME
SELECT revenue, region FROM q2;

Больше того, UNION BY NAME справляется с разным набором колонок. Если в одной таблице есть колонка, которой нет в другой, для строк второй таблицы она заполнится NULL:

-- t1: region, revenue
-- t2: region, revenue, profit
-- В строках из t1 колонка profit станет NULL
SELECT * FROM t1
UNION ALL BY NAME
SELECT * FROM t2;

INSERT ... BY NAME решает ту же задачу для вставки. Обычный INSERT INTO t SELECT ... сопоставляет колонки источника со столбцами таблицы по позиции — и если порядок не совпал, данные молча попадут не в те столбцы. INSERT INTO t BY NAME SELECT ... сопоставляет по именам:

-- Источник возвращает колонки в порядке (revenue, region),
-- а таблица events объявлена как (region, revenue, loaded_at)

-- BY NAME: revenue -> revenue, region -> region,
-- отсутствующая loaded_at получит DEFAULT
INSERT INTO events BY NAME
SELECT 500 AS revenue, 'EU' AS region;

BY NAME при вставке устойчив к двум вещам: к разному порядку колонок и к отсутствию части колонок (недостающие берут значение DEFAULT). Это делает INSERT ... BY NAME гораздо безопаснее в пайплайнах, где схема источника может слегка отличаться от схемы целевой таблицы.

Какой джойн для какой задачи

Общая идея этих расширений — снять ограничение «соединять можно только по точному равенству ключей и только по позиции колонок». ASOF снимает требование точного совпадения, LATERAL — изоляцию сторон, POSITIONAL меняет ключ на позицию строки, BY NAME меняет позицию колонки на её имя. Каждое — про конкретный реальный сценарий, в котором обычный JOIN либо невозможен, либо требует громоздкого обходного пути.

Попробуй сам

Создайте таблицы:

CREATE TABLE trades AS
  SELECT * FROM (VALUES
    (TIMESTAMP '2026-01-01 10:32:15', 'AAA', 10),
    (TIMESTAMP '2026-01-01 10:36:00', 'AAA', 5),
  ) t(trade_time, symbol, qty);

CREATE TABLE quotes AS
  SELECT * FROM (VALUES
    (TIMESTAMP '2026-01-01 10:30:00', 'AAA', 99.0),
    (TIMESTAMP '2026-01-01 10:32:00', 'AAA', 101.0),
    (TIMESTAMP '2026-01-01 10:35:00', 'AAA', 103.0),
  ) t(quote_time, symbol, price);

Задания:

1. Через ASOF JOIN сопоставьте каждой сделке последнюю котировку на её момент. Проверьте, что сделка в 10:32:15 получила цену 101.0, а не 103.0.
2. Замените ASOF JOIN на ASOF LEFT JOIN и добавьте сделку с временем 10:00:00 — раньше всех котировок. Посмотрите, что попадёт в price.
3. Создайте таблицу пользователей с заказами и через LATERAL достаньте по два последних заказа на пользователя. Затем перепишите тот же запрос через QUALIFY с row_number().
4. Создайте две одноколоночные таблицы и соедините их через POSITIONAL JOIN. Сделайте одну длиннее и посмотрите на NULL в коротких строках.
5. Создайте две таблицы с колонками region, revenue, но во второй поменяйте порядок на revenue, region. Склейте их обычным UNION ALL и через UNION ALL BY NAME. Сравните результаты.