QA Interview Hub · База знань · Тести

SQL

JOIN

типи з'єднань і їхні пастки

Що таке JOIN і навіщо він потрібен?

Дані розкладені по різних таблицях, щоб не дублюватися: email зберігається один раз у users, а не в кожній транзакції. JOIN з'єднує таблиці за умовою — зазвичай зовнішній ключ дорівнює первинному — і дає змогу отримати повну картину в одному запиті.

-- транзакції з email власника
SELECT t.id, t.amount, t.status, u.email
FROM transactions t
JOIN accounts a ON a.id = t.account_id
JOIN users    u ON u.id = a.user_id;
На співбесіді: JOIN з'єднує таблиці за умовою, зазвичай за зовнішнім ключем. Дані зберігаються в різних таблицях, щоб не дублюватися, а JOIN збирає їх разом: наприклад, транзакцію, рахунок і email власника в одному запиті.

Як працює INNER JOIN?

Повертає лише ті рядки, для яких знайшлася пара в обох таблицях. Рядки без пари зникають з результату. Слово INNER можна не писати — JOIN за замовчуванням внутрішній.

Наслідок для QA: якщо в результаті INNER JOIN «не вистачає» записів — можливо, у них немає пари, і це саме по собі може бути багом даних.

-- лише користувачі, у яких є хоч один рахунок
SELECT u.email, a.currency, a.balance
FROM users u
INNER JOIN accounts a ON a.user_id = u.id;
На співбесіді: INNER JOIN повертає лише ті рядки, для яких є пара в обох таблицях, а решта зникає. Тому якщо після JOIN записів менше, ніж очікувалося, я перевіряю, чи немає записів без пари, — це може бути проблема в даних.

Як працює LEFT JOIN?

Повертає всі рядки з лівої таблиці, а з правої — ті, що знайшли пару. Якщо пари немає, стовпці правої таблиці заповнюються NULL.

-- всі користувачі, навіть без рахунків
SELECT u.email, a.id AS account_id, a.balance
FROM users u
LEFT JOIN accounts a ON a.user_id = u.id;

-- результат:
-- anna@test.com | 10 | 500.00
-- ivan@test.com | 11 | 0.00
-- olha@test.com | NULL | NULL     <- рахунку немає
На співбесіді: LEFT JOIN повертає всі рядки з лівої таблиці, а з правої — лише ті, що мають пару, інакше там NULL. Я використовую його, коли важливо не загубити записи: наприклад, показати всіх користувачів, у тому числі тих, у кого ще немає рахунку.

Що таке RIGHT, FULL OUTER і CROSS JOIN?

  • RIGHT JOIN — дзеркало LEFT: усі рядки з правої таблиці. На практиці майже завжди замінюють на LEFT, поміняючи таблиці місцями, — так читабельніше.
  • FULL OUTER JOIN — усі рядки з обох таблиць; де пари немає — NULL. Корисний для звірки двох наборів даних. У MySQL його немає — емулюють через UNION двох LEFT JOIN.
  • CROSS JOIN — кожен рядок з кожним (декартів добуток). 1000 × 1000 = мільйон рядків. Іноді корисний для генерації комбінацій тестових даних.
-- звірка: транзакції в нас проти виписки провайдера
SELECT o.id AS our_id, p.external_id AS provider_id
FROM transactions o
FULL OUTER JOIN provider_report p ON p.external_id = o.external_id
WHERE o.id IS NULL OR p.external_id IS NULL;   -- є лише з одного боку
На співбесіді: RIGHT JOIN — дзеркало LEFT, і зазвичай його замінюють на LEFT, поміняючи таблиці місцями. FULL OUTER JOIN повертає все з обох боків, і він ідеальний для звірки двох наборів даних — наприклад, наших транзакцій з випискою провайдера. CROSS JOIN дає всі комбінації рядків, тому з ним треба бути обережним.

Як знайти записи, які не мають пари в іншій таблиці?

Класичний прийом — anti-join: LEFT JOIN і умова, що ключ правої таблиці IS NULL. Альтернатива — NOT EXISTS.

Для QA це один із найкорисніших запитів: «осиротілі» записи — часта ознака бага.

-- користувачі без жодного рахунку
SELECT u.id, u.email
FROM users u
LEFT JOIN accounts a ON a.user_id = u.id
WHERE a.id IS NULL;

-- те саме через NOT EXISTS
SELECT u.id, u.email
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM accounts a WHERE a.user_id = u.id);
На співбесіді: Записи без пари я шукаю через LEFT JOIN з умовою, що ключ правої таблиці IS NULL, або через NOT EXISTS. Це один з моїх основних запитів для перевірки цілісності: наприклад, користувачі без рахунку після реєстрації чи транзакції без рахунку означають, що десь процес обірвався посередині.

Яка різниця між умовою в ON і в WHERE при LEFT JOIN?

  • Умова в ON впливає на те, які рядки правої таблиці приєднуються. Усі рядки лівої лишаються.
  • Умова в WHERE фільтрує вже готовий результат. Рядки без пари мають NULL, і умова a.status = 'active' для них дає UNKNOWN — вони зникають. LEFT JOIN непомітно перетворюється на INNER.
-- всі користувачі + їхні активні рахунки (якщо є)
SELECT u.email, a.id
FROM users u
LEFT JOIN accounts a ON a.user_id = u.id AND a.status = 'active';

-- пастка: користувачі без активного рахунку зникнуть
SELECT u.email, a.id
FROM users u
LEFT JOIN accounts a ON a.user_id = u.id
WHERE a.status = 'active';
На співбесіді: При LEFT JOIN умова на праву таблицю в ON лише обмежує, що приєднується, а всі ліві рядки лишаються. А та сама умова у WHERE відкидає рядки, де справа NULL, і LEFT JOIN фактично стає INNER. Це тонка помилка: запит працює без помилок, але повертає менше даних, ніж треба.

Чому після JOIN кількість рядків зросла, а сума задвоїлась?

Якщо з'єднання — «один до багатьох», кожен рядок з боку «один» повторюється стільки разів, скільки в нього пар. Це нормально, але агрегати по стовпцях з боку «один» після такого JOIN стають неправильними.

Приклад: у користувача 2 рахунки й 5 транзакцій. JOIN users → accounts → transactions дає 5 рядків, і SUM(a.balance) врахує баланс кожного рахунку кілька разів.

Як уникнути: агрегувати до JOIN у підзапиті чи CTE, або рахувати з таблиці потрібного рівня.

-- неправильно: баланс рахунку множиться на кількість його транзакцій
SELECT u.id, SUM(a.balance), COUNT(t.id)
FROM users u
JOIN accounts a ON a.user_id = u.id
LEFT JOIN transactions t ON t.account_id = a.id
GROUP BY u.id;

-- правильно: агрегуємо транзакції окремо, потім з'єднуємо
WITH tx AS (
  SELECT account_id, COUNT(*) AS tx_cnt FROM transactions GROUP BY account_id
)
SELECT u.id, SUM(a.balance) AS total_balance, COALESCE(SUM(tx.tx_cnt), 0) AS tx_cnt
FROM users u
JOIN accounts a ON a.user_id = u.id
LEFT JOIN tx ON tx.account_id = a.id
GROUP BY u.id;
На співбесіді: При з'єднанні «один до багатьох» рядок з боку «один» дублюється для кожної пари, і агрегати по його стовпцях задвоюються. Тому, коли рахую суми по кількох рівнях — користувач, рахунки, транзакції, — я спочатку агрегую дочірню таблицю в підзапиті, а потім з'єдную. А як QA я використовую цю пастку як підказку: якщо звіт показує задвоєні суми, перше, що я перевіряю, — JOIN.

Що таке self join?

З'єднання таблиці з самою собою, коли рядки посилаються на інші рядки тієї ж таблиці. Класика — співробітник і його менеджер в одній таблиці employees. Обов'язково потрібні різні аліаси.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
На співбесіді: Self join — це з'єднання таблиці з самою собою, коли рядок посилається на інший рядок тієї ж таблиці, як співробітник на менеджера. Таблицю підключають двічі з різними аліасами. LEFT JOIN тут потрібен, щоб не загубити керівника верхнього рівня, у якого менеджера немає.

Типи JOIN і користувачі без замовлень

INNER JOIN повертає лише рядки, які мають збіг в обох таблицях. LEFT JOIN повертає всі рядки лівої таблиці та збіги з правої, а де збігу немає — NULL. RIGHT JOIN робить те саме навпаки, FULL JOIN повертає всі рядки з обох таблиць.

CROSS JOIN дає декартів добуток — кожен рядок з кожним. SELF JOIN — з'єднання таблиці з самою собою, наприклад працівник і його менеджер у тій самій таблиці. Умова з'єднання задається через ON.

Користувачі без замовлень — класична задача на LEFT JOIN з перевіркою на NULL:

SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

Альтернатива — WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id). Варіант з NOT IN небезпечний: якщо в підзапиті є NULL, він не поверне жодного рядка.