🟢 Junior Level

Главная разница: INNER JOIN возвращает только строки с совпадениями в обеих таблицах, а LEFT JOIN возвращает все строки из левой таблицы, даже если совпадений нет.

Простой пример:

-- Таблица users: Иванов, Петров, Сидоров
-- Таблица orders: Иванов (заказ), Петров (заказ)

-- INNER JOIN: только пользователи с заказами
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Результат: Иванов, Петров

-- LEFT JOIN: все пользователи, даже без заказов
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Результат: Иванов (100), Петров (200), Сидоров (NULL)

Когда использовать:

• INNER JOIN — когда нужны только связанные данные
• LEFT JOIN — когда нужны все записи из основной таблицы + возможные связанные

---

🟡 Middle Level

Свобода перестановки (Join Reordering)

INNER JOIN — ассоциативен и коммутативен:

-- Планировщик может соединять таблицы в любом порядке:
(A JOIN B) JOIN C = A JOIN (B JOIN C) = (A JOIN C) JOIN B

Это даёт максимум свободы для выбора оптимального плана.

LEFT JOIN — НЕ ассоциативен:

-- Порядок жёстко ограничен:
(A LEFT JOIN B) LEFT JOIN C ≠ A LEFT JOIN (B LEFT JOIN C)

Планировщик ограничен в перестановках → может выбрать субоптимальный план.

Физические стратегии (как PostgreSQL выполняет JOIN)

PostgreSQL использует три алгоритма для физического выполнения JOIN:

• Nested Loop — для каждой строки внешней таблицы ищет совпадения во внутренней (быстро, если есть индекс)
• Hash Join — строит хэш-таблицу по меньшей таблице и ищет совпадения (быстро для больших таблиц без индекса)
• Merge Join — обе таблицы сортируются и сливаются как два отсортированных массива (быстро, если данные уже отсортированы)

Физические стратегии

Метрика	INNER JOIN	LEFT JOIN
Hash Join	Хэш строится по меньшей таблице	Хэш, как правило, по правой таблице
Nested Loop	Любая таблица может быть внешней	Левая таблица, как правило, внешняя
Merge Join	Обе таблицы могут быть отсортированы	Левая таблица определяет порядок

Ловушка WHERE (Outer-to-Inner Transformation)

PostgreSQL может превращать LEFT JOIN в INNER JOIN “на лету”:

-- ❌ LEFT JOIN превратился в INNER JOIN!
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;
-- NULL > 100 = UNKNOWN → все пользователи без заказов отфильтрованы!

-- ✅ Правильно: фильтр в ON
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.amount > 100;
-- Все пользователи сохранятся, заказы фильтруются

-- ✅ Или фильтр на IS NULL
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;  -- Пользователи БЕЗ заказов (Anti-Join)

Типичные ошибки

1. Путаница с NULL

   -- LEFT JOIN вернёт NULL для отсутствующих данных
   SELECT u.name, SUM(o.amount)
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id
   GROUP BY u.name;
      
   -- NULL + число = NULL!
   -- Решение: COALESCE
   SELECT u.name, COALESCE(SUM(o.amount), 0) as total
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id
   GROUP BY u.name;
   

2. Дублирование строк

   -- ❌ Если у пользователя 5 заказов, он появится 5 раз!
   SELECT u.name, u.email
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id;
      
   -- ✅ Группируйте или используйте EXISTS
   SELECT DISTINCT u.name, u.email  -- или GROUP BY
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id;
   

3. LEFT JOIN там, где нужен INNER

   -- ❌ LEFT JOIN замедляет (меньше свободы у планировщика)
   SELECT u.name, o.amount
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id
   WHERE o.amount IS NOT NULL;  -- Фактически INNER JOIN
      
   -- ✅ Используйте INNER JOIN
   SELECT u.name, o.amount
   FROM users u
   INNER JOIN orders o ON u.id = o.user_id;
   

Практическое сравнение

-- INNER JOIN: только заказы с клиентами
SELECT u.name, COUNT(o.id) as orders_count
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- Только клиенты с заказами

-- LEFT JOIN: все клиенты + статистика
SELECT u.name, COUNT(o.id) as orders_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.name;
-- Все клиенты, у некоторых orders_count = 0

---

🔴 Senior Level

Когда НЕ использовать LEFT JOIN

1. Когда данные гарантированно есть в обеих таблицах — используйте INNER JOIN, он даёт оптимизатору больше свободы
2. Когда ORM генерирует LEFT JOIN «на всякий случае» — проверьте, реально ли нужны данные из правой таблицы; если нет — Outer Join Removal удалит JOIN, но лучше не генерировать его
3. Каскады 5+ LEFT JOIN — планировщик ограничен в перестановках, может выбрать катастрофически плохой план

Join Reordering: Глубокий анализ

INNER JOIN даёт оптимизатору полную свободу:

Запрос: A JOIN B JOIN C JOIN D (4 таблицы)

Возможных порядков: 4! = 24 варианта
Планировщик выбирает самый дешёвый.

LEFT JOIN ограничивает:

Запрос: A LEFT JOIN B LEFT JOIN C LEFT JOIN D

Возможных порядков: только 1 (A → B → C → D)
Планировщик может только выбирать физические стратегии.

Влияние на производительность:

• На сложных схемах (10+ таблиц) цепочки LEFT JOIN могут привести к катастрофически плохому плану
• Решение: Используйте INNER JOIN где возможно, LEFT JOIN только где семантически необходим

Outer Join Removal (PG 10+)

Мощная оптимизация — PostgreSQL удаляет ненужный LEFT JOIN:

Условия удаления:

1. Не выбираете ни одной колонки из правой таблицы
2. Ключ в правой таблице UNIQUE или PRIMARY KEY

-- LEFT JOIN будет удалён из плана!
SELECT u.name FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- orders не используется в SELECT + user_id UNIQUE → удаление

-- Проверка через EXPLAIN
EXPLAIN SELECT u.name FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Seq Scan on users  ← orders НЕТ в плане!

Зачем это происходит:

• Часто ORM или универсальные Views генерируют LEFT JOIN “на всякий случай”
• Оптимизатор убирает лишнюю работу

Outer-to-Inner Transformation

Оптимизатор превращает LEFT JOIN в INNER JOIN, если WHERE делает NULL невозможными:

-- Исходный запрос
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

-- После трансформации (внутренне)
SELECT u.name, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;

-- Почему? NULL > 100 = UNKNOWN → строки с NULL всё равно отфильтруются
-- Значит, LEFT JOIN семантически эквивалентен INNER JOIN

Проверка:

-- Увидеть трансформации
EXPLAIN (VERBOSE) 
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;
-- В выводе увидите: "Join Type: Inner"

Оценка кардинальности (Cardinality Estimation)

INNER JOIN:

rows = size(A) × size(B) × selectivity(join_condition)

Пример:
- users: 1,000,000 строк
- orders: 10,000,000 строк
- selectivity: 0.001 (0.1%)

rows = 1,000,000 × 10,000,000 × 0.001 = 10,000,000,000 × 0.001 = 10,000,000

⚠️ Ошибка в статистике может привести к оценке в 10x больше/меньше!

LEFT JOIN:

rows >= size(левая таблица)

Планировщик знает, что результат будет как минимум не меньше левой таблицы.
Это делает планы более предсказуемыми, но менее гибкими.

LEFT JOIN LATERAL

Мощный инструмент для сложных выборок:

-- "Найти топ-3 заказа для каждого пользователя"
SELECT u.name, top_orders.amount, top_orders.created_at
FROM users u
LEFT JOIN LATERAL (
    SELECT amount, created_at
    FROM orders o
    WHERE o.user_id = u.id
    ORDER BY created_at DESC
    LIMIT 3
) top_orders ON true;

-- Физический план: Nested Loop
-- Для каждого пользователя → 3 заказа из индекса
-- Отлично для users < 10,000
-- Медленно для users > 1,000,000

Альтернатива для больших данных:

-- Window Function вместо LATERAL
SELECT name, amount, created_at
FROM (
    SELECT u.name, o.amount, o.created_at,
           ROW_NUMBER() OVER(PARTITION BY u.id ORDER BY o.created_at DESC) as rn
    FROM users u
    INNER JOIN orders o ON u.id = o.user_id
) sub
WHERE rn <= 3;

-- План: Hash Join + WindowAgg
-- Намного быстрее для больших таблиц!

Edge Cases

1. Смешивание INNER и LEFT JOIN

   -- Порядок важен!
   SELECT *
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id
   INNER JOIN products p ON o.product_id = p.id;
      
   -- Это НЕ то же самое, что:
   SELECT *
   FROM users u
   INNER JOIN orders o ON u.id = o.user_id
   LEFT JOIN products p ON o.product_id = p.id;
      
   -- Первый: все пользователи, заказы (если есть), продукты (если есть заказ)
   -- Второй: только пользователи с заказами, продукты (если есть)
   

2. Множественные LEFT JOIN

   -- Каскад LEFT JOIN может дать неожиданный результат
   SELECT u.name, o.id as order_id, p.id as product_id
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id
   LEFT JOIN products p ON o.product_id = p.id;
      
   -- Если у пользователя нет заказов → order_id = NULL, product_id = NULL
   -- Если есть заказ, но нет продукта → order_id = 123, product_id = NULL
   

3. Коалесценция с NULL

   -- Агрегации с LEFT JOIN
   SELECT u.name, 
          COUNT(o.id) as orders_count,        -- 0 если нет заказов
          SUM(o.amount) as total_amount,       -- NULL если нет заказов!
          AVG(o.amount) as avg_amount          -- NULL если нет заказов!
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id
   GROUP BY u.name;
      
   -- Решение: COALESCE
   SELECT u.name, 
          COUNT(o.id) as orders_count,
          COALESCE(SUM(o.amount), 0) as total_amount,
          COALESCE(AVG(o.amount), 0) as avg_amount
   FROM users u
   LEFT JOIN orders o ON u.id = o.user_id
   GROUP BY u.name;
   

Performance Comparison

Сценарий	INNER JOIN	LEFT JOIN
Планирование	Быстрее (больше вариантов)	Медленнее (ограничения)
Nested Loop	Любая таблица driving	Левая всегда driving
Hash Join	Гибкий выбор building	Правая всегда building
Merge Join	Обе таблицы сортируются	Левая определяет порядок
Index Usage	Гибкий	Зависит от направления

Production Experience

Реальный сценарий #1: LEFT JOIN убивает производительность

• Отчёт: 15 таблиц, 12 из них через LEFT JOIN “на всякий случай”
• Проблема: Запрос 45 секунд вместо ожидаемых 2
• EXPLAIN: Nested Loop с левой таблицей всегда driving → катастрофа
• Решение:
  • Заменили 8 LEFT JOIN на INNER JOIN (данные всегда есть)
  • Разбили на 2 CTE
• Результат: Запрос < 3 секунды

Реальный сценарий #2: Outer Join Removal не сработал

• API: LEFT JOIN на View с UNIQUE constraint
• Ожидали: удаление JOIN оптимизатором
• Реальность: constraint был DEFERRABLE → оптимизатор не уверен в UNIQUE
• Решение: Заменили DEFERRABLE на NOT DEFERRABLE → removal сработал
• Результат: Запрос ускорился в 5 раз

Monitoring

-- 1. Проверка планов с LEFT JOIN
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- 2. Проверка на Outer Join Removal
EXPLAIN (VERBOSE) 
SELECT u.name FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Ищите "Join Type: Inner" или отсутствие orders в плане

-- 3. Проверка трансформации Outer-to-Inner
EXPLAIN (VERBOSE)
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;
-- "Join Type: Inner" → трансформация произошла

-- 4. Статистика по JOIN в pg_stat_statements
SELECT query, mean_exec_time, calls
FROM pg_stat_statements
WHERE query LIKE '%LEFT JOIN%'
ORDER BY mean_exec_time DESC
LIMIT 10;

Best Practices

1. Используйте INNER JOIN где семантически возможно
2. LEFT JOIN только где нужно сохранить все записи из левой таблицы
3. Фильтры на правой таблице ставьте в ON, а не в WHERE
4. Проверяйте план через EXPLAIN (VERBOSE) на удаление JOIN
5. Избегайте каскадов LEFT JOIN > 5 — планировщик ограничен
6. LATERAL JOIN только для малых внешних выборок
7. COALESCE для агрегаций с LEFT JOIN
8. DISTINCT или GROUP BY при LEFT JOIN чтобы избежать дублирования

Резюме для Senior

• INNER JOIN = максимум свободы для оптимизатора
• LEFT JOIN = ограничения на Join Reordering
• Outer Join Removal (PG 10+) может удалить ненужный LEFT JOIN
• Outer-to-Inner Transformation происходит при фильтрации на NULL
• LEFT JOIN LATERAL ≈ Nested Loop → только для малых внешних выборок
• Фильтры на правой таблице → в ON, не в WHERE
• COALESCE для SUM/AVG с LEFT JOIN (NULL → 0)
• Всегда проверяйте план через EXPLAIN (VERBOSE, ANALYZE)

---

🎯 Шпаргалка для интервью

Обязательно знать:

• INNER JOIN: ассоциативен и коммутативен → оптимизатор может переставлять таблицы
• LEFT JOIN: НЕ ассоциативен → порядок жёстко ограничен
• Outer Join Removal (PG 10+): удаляет LEFT JOIN если правая таблица не используется в SELECT
• Outer-to-Inner Transformation: WHERE на правой таблице превращает LEFT JOIN в INNER
• Hash Join: для LEFT JOIN хэш ВСЕГДА по правой таблице (для INNER — гибко)
• Nested Loop: для LEFT JOIN левая таблица ВСЕГДА driving
• Фильтры на правой таблице → в ON (сохранит NULL), не в WHERE (отфильтрует NULL)
• COALESCE(SUM(…), 0) — SUM с LEFT JOIN вернёт NULL при отсутствии данных

Частые уточняющие вопросы:

• «Почему LEFT JOIN медленнее INNER JOIN?» → Меньше свободы для Join Reordering
• «Когда Outer Join Removal не сработает?» → DEFERRABLE constraint, VIEW
• «Что будет, если фильтр в WHERE вместо ON для LEFT JOIN?» → Отфильтрует NULL-строки
• «Когда LATERAL JOIN катастрофа?» → Внешняя таблица > 10,000 строк

Красные флаги (НЕ говорить):

• ❌ «LEFT JOIN всегда безопаснее» (он медленнее и ограничен для оптимизатора)
• ❌ «Фильтр на правой таблице можно в WHERE» (превратит LEFT JOIN в INNER!)
• ❌ «SUM с LEFT JOIN вернёт 0» (вернёт NULL! Нужен COALESCE)

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

• [[Какие типы JOIN существуют]] → физические стратегии выполнения
• [[Что лучше JOIN или подзапрос]] → когда EXISTS лучше JOIN
• [[Что такое коррелированный подзапрос]] → EXISTS как альтернатива