Java Interview Questions

Головна / Усі відповіді / Бази даних SQL PostgreSQL

Питання 8 · Розділ 1

В чому різниця між INNER JOIN та LEFT JOIN?

Це дає максимум свободи для вибору оптимального плану.

Ukrainian Бази даних SQL PostgreSQL Вихідний Markdown
Мовні версії: English Russian Ukrainian

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