---

🟢 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 як альтернатива