JOIN: все виды соединений

45 секунд против 0.3 секунды

2019 год. Booking.com. Запрос с 7 JOIN'ами без LATERAL выполнялся 45 секунд. Для каждого отеля из основной выборки база данных запрашивала цены отдельным подзапросом - без корреляции, без возможности использовать индекс на параметр даты внутри вложенного запроса. Один рефактор: обычные JOIN заменены на LATERAL, добавлен индекс на колонку внутри подзапроса. Результат: 0.3 секунды. Разница в 150 раз. Одно ключевое слово изменило характер работы планировщика - с полного перебора на адресный поиск. LATERAL - это не синтаксический сахар. Это другая семантика выполнения.

JOIN - не способ склеить таблицы. Это инструкция планировщику о том, какой алгоритм применить к двум наборам данных. INNER JOIN исключает строки без пары. LEFT JOIN сохраняет сирот. LATERAL позволяет каждой строке левой таблицы иметь собственный подзапрос с её параметрами. Разница между ними - не стилистическая. Это разница между 45 секундами и 0.3.

• **E-commerce отчёты**: LEFT JOIN товаров с заказами - сохраняет товары без продаж, которые обычный INNER JOIN скрывает
• **LATERAL для топ-N на группу**: для каждого пользователя получить 3 последних заказа - классический кейс LATERAL + LIMIT
• **Anti-join через NOT EXISTS**: найти клиентов без ни одного заказа - NOT EXISTS эффективнее LEFT JOIN ... WHERE IS NULL на больших таблицах
• **Самосоединение для иерархий**: категории товаров, дерево сотрудников - employees e JOIN employees m ON e.manager_id = m.id
• **CROSS JOIN для матриц**: все комбинации размеров и цветов для генерации SKU - CROSS JOIN без условия даёт декартово произведение

INNER, LEFT, RIGHT, FULL OUTER JOIN

Каждый тип JOIN - это политика обработки строк без пары. INNER JOIN самый строгий: строка попадает в результат только если нашла совпадение с обеих сторон. Нет пары - строки нет. Это поведение ломает наивные отчёты: запрос "все товары с продажами" через INNER JOIN молча выбрасывает товары, которые ни разу не купили.

LEFT JOIN меняет семантику: все строки левой таблицы гарантированно попадают в результат. Если пары нет - правая сторона заполняется NULL. Это стандартный паттерн для "найти всех плюс их данные если есть". RIGHT JOIN - зеркало LEFT JOIN с другой ведущей стороной. На практике RIGHT JOIN почти не используется - проще поменять таблицы местами.

FULL OUTER JOIN - редкий зверь. Возвращает все строки обеих таблиц, NULL там где нет пары с противоположной стороны. Используется для сравнения двух наборов данных: например, найти расхождения между двумя источниками одного справочника.

**Anti-join паттерн**: найти строки без пары - это LEFT JOIN + WHERE right.id IS NULL. Альтернатива - NOT EXISTS. На больших таблицах NOT EXISTS с индексом часто быстрее, потому что планировщик останавливается при первом совпадении. ```sql -- Клиенты без единого заказа SELECT c.id, c.email FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.id ); ```

CROSS JOIN, LATERAL JOIN и self-join

CROSS JOIN - декартово произведение. Каждая строка левой таблицы соединяется с каждой строкой правой. 100 строк × 50 строк = 5000 строк результата. Нет условия ON - нет фильтрации. Используется для генерации комбинаций: все размеры одежды × все цвета, все временные слоты × все ресурсы. Случайный CROSS JOIN на больших таблицах убивает базу.

LATERAL JOIN - принципиально другой зверь. Ключевое слово LATERAL позволяет подзапросу справа обращаться к колонкам строки слева. Это коррелированный подзапрос в синтаксисе FROM. Без LATERAL подзапрос в FROM выполняется один раз для всего запроса. С LATERAL - один раз для каждой строки левой таблицы, получая её данные как параметры.

Именно этот паттерн описан в кейсе Booking.com. Без LATERAL база применяла один подзапрос ко всем строкам, не имея доступа к параметрам каждой строки - и не могла использовать индекс. С LATERAL каждая итерация получала конкретный customer_id и дату - планировщик переключался на Index Scan. 150-кратное ускорение от одного слова.

**LATERAL с функциями**: LATERAL позволяет вызывать set-returning functions (SRF) для каждой строки. Например, `CROSS JOIN LATERAL unnest(tags) AS tag` разворачивает массив тегов из каждой строки в отдельные строки.

Алгоритмы JOIN и EXPLAIN ANALYZE

PostgreSQL выбирает из трёх алгоритмов для каждого JOIN в запросе. Nested Loop берёт каждую строку внешней таблицы и ищет совпадение во внутренней. Без индекса - O(n * m). С индексом на join-колонке внутренней таблицы - O(n * log m). Это Index Nested Loop Join - самый быстрый вариант для небольших внешних таблиц.

Hash Join - рабочая лошадка для больших таблиц без сортировки. PostgreSQL строит хеш-таблицу из меньшей таблицы в памяти (work_mem), затем проходит по большей, ища совпадения через хеш. O(n + m). Если хеш-таблица не помещается в work_mem - начинается батч-режим со сбросом на диск. EXPLAIN ANALYZE покажет `Batches: 1` (в памяти) или `Batches: N` (spillage на диск).

Merge Join требует обе таблицы отсортированными по join-колонке. Если индексы уже обеспечивают порядок - Merge Join бесплатно получает отсортированный поток и соединяет за один проход: O(n + m). Если сортировки нет - PostgreSQL добавит Sort узел, что обнуляет преимущество. Merge Join редко выбирается в OLTP, но встречается в аналитических запросах по диапазонам дат.

**Кардинальность и оценки планировщика**: если EXPLAIN показывает `rows=1000` а `actual rows=500000` - статистика устарела или JOIN-условие нетипично. Запустить `ANALYZE table_name` для обновления статистики. Сильное расхождение заставляет планировщик выбирать неоптимальный алгоритм JOIN.

Ключевые идеи

• **INNER JOIN**: только строки с совпадением с обеих сторон - исключает NULL-пары
• **LEFT/RIGHT JOIN**: все строки ведущей стороны, NULL для несовпавших со второй
• **FULL OUTER JOIN**: все строки обеих сторон, NULL там где нет пары - симметричный LEFT + RIGHT
• **CROSS JOIN**: декартово произведение без условия соединения - O(n*m) строк
• **LATERAL JOIN**: подзапрос справа видит колонки строки слева - коррелированный подзапрос в синтаксисе FROM
• **Anti-join**: NOT EXISTS или NOT IN или LEFT JOIN ... WHERE IS NULL - строки без пары во второй таблице
• **Алгоритм определяет стоимость**: Hash Join для больших без сортировки, Nested Loop для маленьких с индексом, Merge Join для отсортированных - EXPLAIN ANALYZE покажет реальный выбор

Связанные темы

JOIN - точка пересечения синтаксиса SQL и физики выполнения запросов:

• EXPLAIN ANALYZE — Покажет реальный алгоритм и стоимость каждого JOIN
• Алгоритмы JOIN — Hash Join, Nested Loop, Merge Join - физика за ключевым словом
• Подзапросы — LATERAL - это коррелированный подзапрос в синтаксисе JOIN
• Индексы B-tree — Index Nested Loop Join требует индекса на join-колонке
• GROUP BY и агрегация — JOIN + GROUP BY - классический аналитический паттерн

Вопросы для размышления

• Запрос возвращает меньше строк чем ожидается. Какой тип JOIN использовался и почему это произошло?
• В каком случае LEFT JOIN ... WHERE right.id IS NULL предпочтительнее NOT EXISTS, и наоборот?
• LATERAL JOIN в запросе замедляет выполнение вместо ускорения. Какой индекс отсутствует?

Связанные уроки

• pg-06-select — SELECT - база: без него JOIN не прочитать
• pg-08-aggregation — GROUP BY после JOIN - стандартный аналитический паттерн
• pg-09-subqueries — LATERAL JOIN - это коррелированный подзапрос в синтаксисе JOIN
• pg-18-explain — EXPLAIN ANALYZE показывает реальный алгоритм JOIN и его стоимость
• pg-19-join-algorithms — Hash Join / Merge Join / Nested Loop - физика за синтаксисом JOIN
• pg-12-btree — Индекс на join-колонке переключает Nested Loop с O(n*m) на O(n*log m)
• db-06-sql-advanced