vyvchy
    Теми розділу

    05 · Бази даних і SQL для QA

    JOIN: типи з'єднань таблиць

    Зміст

    У реляційній базі дані навмисно розкидані по таблицях: замовлення не зберігає імʼя клієнта, воно тримає лише customer_id — посилання на рядок в іншій таблиці (див. Реляційна модель). Це економить місце й уникає розсинхрону, але означає, що жодна окрема таблиця не показує повної картини. Щоб побачити «замовлення № 101 клієнта Ігоря з Києва на 3 позиції», ці шматки треба зібрати назад. Саме це й робить JOIN — зʼєднує рядки двох чи більше таблиць за умовою відповідності.

    Для QA JOIN — це не академічна вправа, а щоденний інструмент і майже гарантоване питання на співбесіді. Ним звіряють, що UI показує правильно зшиті дані; шукають «сирітські» (orphan) записи, які посилаються в нікуди; ловлять роздуті звіти, де сума подвоїлась через невдалий JOIN. Помилка в одному ключовому слові — LEFT замість INNER чи умова не в тому місці — тихо змінює результат, і зіпсований звіт виглядає правдоподібно. Тому механіку треба розуміти, а не завчати назви.

    INNER JOIN: лишаються тільки збіги

    INNER JOIN (або просто JOIN — це синоніми) бере рядок лівої таблиці, шукає для нього відповідні рядки правої за умовою ON, і повертає лише ті пари, де умова істинна. Рядок, для якого пари немає з жодного боку, у результат не потрапляє взагалі.

    SELECT o.id, o.total, c.name
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id;

    Умова після ON — це серце зʼєднання. Найчастіше вона звіряє зовнішній ключ (foreign key) однієї таблиці з первинним ключем (primary key) іншої: o.customer_id = c.id. Але ON — звичайний логічний вираз: там можуть бути кілька умов через AND, нерівності, порівняння діапазонів. Аліаси (o, c) не обовʼязкові, та коли таблиць кілька або їхні стовпці однойменні, без них запит стає нечитабельним і неоднозначним.

    Ключовий наслідок, який часто забувають: INNER JOIN симетричний і водночас відсіює. Якщо в orders є замовлення з customer_id, якого немає в customers, — таке замовлення зникне з результату мовчки. Якщо клієнт не зробив жодного замовлення — його теж не буде. INNER JOIN показує лише перетин.

    LEFT JOIN: зберегти все зліва, доповнити NULL

    Часто перетину замало. «Покажи всіх клієнтів разом із їхніми замовленнями, а хто нічого не купив — теж покажи» — це вже про LEFT JOIN (повна назва LEFT OUTER JOIN). Він гарантує: кожен рядок лівої таблиці буде в результаті. Якщо для нього знайшлась пара справа — стовпці правої таблиці заповнені; якщо ні — вони заповнені NULL.

    SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;

    Клієнт без замовлень тут зʼявиться одним рядком, де order_id дорівнює NULL. Це прямий місток до теми пасток NULL: NULL після outer-джойна означає не «нуль» і не «порожньо», а «пари не знайшлося».

    RIGHT JOIN — дзеркальна версія: зберігає всі рядки правої таблиці. На практиці його пишуть рідко, бо будь-який RIGHT JOIN переписується як LEFT JOIN простою перестановкою таблиць, а читати запит, де всі джойни ліві, легше. Знати RIGHT треба, застосовувати — здебільшого ні.

    Сирітські записи: анти-джойн

    LEFT JOIN дає елегантний спосіб знайти записи без пари — те, що на співбесідах формулюють як «знайди клієнтів, які нічого не замовляли». Роблять outer-джойн, а потім лишають тільки ті рядки, де права сторона виявилась NULL:

    -- клієнти без жодного замовлення
SELECT c.id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

    Цей патерн називають анти-джойном (anti-join). Він же ловить биту цілісність — замовлення, що посилається на неіснуючого клієнта:

    -- сирітські замовлення: customer_id вказує в нікуди
SELECT o.id, o.customer_id
FROM orders AS o
LEFT JOIN customers AS c ON c.id = o.customer_id
WHERE c.id IS NULL;

    Для QA це готова перевірка посилальної цілісності: у здоровій базі з правильними зовнішніми ключами такий запит має повертати нуль рядків. Якщо повертає — маєте баг даних, і це канонічна тема глави Тестування БД.

    FULL OUTER і CROSS

    FULL OUTER JOIN — обʼєднання обох боків: зберігає і рядки лівої таблиці без пари, і рядки правої без пари, доповнюючи відсутню сторону NULL. Він відповідає на питання «покажи все з обох таблиць, а де немає відповідності — познач». Зручно для звірки двох наборів: які записи є лише зліва, які лише справа, які збіглися.

    SELECT c.name, o.id
FROM customers AS c
FULL OUTER JOIN orders AS o ON o.customer_id = c.id;

    Пастка діалектів: PostgreSQL FULL OUTER JOIN підтримує, а MySQL історично — ні. У MySQL його емулюють обʼєднанням лівого й правого джойнів через UNION. Якщо запит із FULL OUTER JOIN падає з синтаксичною помилкою — перше, що варто перевірити, це яка саме СУБД під запитом.

    CROSS JOIN стоїть окремо: у нього немає ON. Він дає декартів добуток (Cartesian product) — кожен рядок лівої таблиці склеюється з кожним рядком правої. Дві таблиці по 10 рядків дадуть 100. Іноді це саме те, що треба — згенерувати всі комбінації розмірів і кольорів:

    SELECT s.size, cl.color
FROM sizes AS s
CROSS JOIN colors AS cl;

    Але значно частіше декартів добуток трапляється випадково — коли таблиці перелічені через кому, а умову зʼєднання забули у WHERE. FROM orders o, customers c без WHERE — це той самий CROSS JOIN, тільки замаскований під старий синтаксис. Результат роздувається, запит гальмує, а звіт бреше. Явний JOIN ... ON тим і кращий за кому, що умова зʼєднання видима: PostgreSQL INNER JOIN без ON узагалі не пропустить, а от MySQL пропустить і мовчки зробить декартів добуток — там JOIN, INNER JOIN і CROSS JOIN синтаксично взаємозамінні.

    Self-join: таблиця зʼєднана сама із собою

    Іноді звʼязок веде всередину тієї самої таблиці. Класика — співробітники, де кожен має manager_id, що посилається на id іншого співробітника в тій самій таблиці employees. Щоб поряд із іменем працівника показати імʼя його керівника, таблицю зʼєднують саму із собою — це self-join. Технічно нічого нового: та сама таблиця згадується двічі під різними аліасами, і без аліасів тут ніяк — інакше СУБД не зрозуміє, про який із двох екземплярів ідеться.

    SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id;

    LEFT JOIN тут не випадковий: у найвищого керівника manager_id дорівнює NULL, і INNER JOIN викинув би його з результату. Self-join застосовують і для ієрархій категорій, і щоб знайти пари рядків з одним значенням (наприклад, двох користувачів з однаковим email) — усюди, де рядки таблиці посилаються одне на одного.

    Дублікати після JOIN: пастка 1:N

    Найпідступніший ефект джойна виникає на звʼязку «один до багатьох» (1:N). Один клієнт має багато замовлень; одне замовлення — багато позицій (order_items). Коли ви зʼєднуєте «одиничну» таблицю з «множинною», рядок з боку «один» повторюється стільки разів, скільки в нього пар з боку «багато».

    клієнт 7 · Ігор

    JOIN orders

    Ігор · замовлення 101

    Ігор · замовлення 102

    Ігор · замовлення 103

    клієнт 7 · Ігор

    JOIN orders

    Ігор · замовлення 101

    Ігор · замовлення 102

    Ігор · замовлення 103

    Сам по собі це не баг — так і має бути, ми ж просили рядок на кожну пару. Баг зʼявляється, коли поверх такого джойна рахують агрегати. Якщо після зʼєднання orders з order_items порахувати SUM(o.total), сума кожного замовлення додасться стільки разів, скільки в ньому позицій, — і виторг магічно виросте. Так само COUNT(*) рахуватиме не клієнтів і не замовлення, а рядки роздутого набору.

    -- НЕБЕЗПЕЧНО: total подвоюється на кожну позицію замовлення
SELECT c.name, SUM(o.total)
FROM customers AS c
JOIN orders AS o      ON o.customer_id = c.id
JOIN order_items AS oi ON oi.order_id = o.id
GROUP BY c.name;

    Це одне з найчастіших питань на співбесіді формату «чому цифра неправильна». Ознака — результат більший за очікуваний рівно в кілька разів. Лікування залежить від задачі: агрегувати «множинну» таблицю окремо (у підзапиті чи CTE — див. Підзапити і CTE) до зʼєднання, або застосувати COUNT(DISTINCT ...) замість COUNT(*). Детальніше про правила агрегації поверх джойнів — у главі Агрегація: GROUP BY і HAVING. Головне — памʼятати, що JOIN за звʼязком 1:N розмножує рядки, і кожен агрегат після нього треба перевіряти окремо.

    Пастка ON vs WHERE у LEFT JOIN

    Ось де ламається інтуїція навіть у тих, хто впевнено знає різницю INNER/LEFT. Питання: куди покласти умову-фільтр на праву таблицю — в ON чи у WHERE? Для INNER JOIN різниці в результаті немає. Для LEFT JOIN вона фундаментальна.

    Щоб зрозуміти чому, треба знати логічний порядок обробки: спершу виконується зʼєднання за умовою ON (і саме тут outer-джойн додає рядки без пари, доповнені NULL), і лише потім по всьому зʼєднаному набору проходить WHERE.

    FROM customers

    LEFT JOIN orders

    ON: умова зʼєднання
    рядки без пари → доповнені NULL

    WHERE: фільтр по зʼєднаному набору
    рядки з NULL можуть відсіятись

    SELECT

    FROM customers

    LEFT JOIN orders

    ON: умова зʼєднання
    рядки без пари → доповнені NULL

    WHERE: фільтр по зʼєднаному набору
    рядки з NULL можуть відсіятись

    SELECT

    Тепер порівняйте два майже однакові запити:

    -- НЕ те, що очікуєш: фактично INNER JOIN
SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid';

-- Правильно: фільтр — усередині ON
SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.status = 'paid';

    У першому запиті LEFT JOIN чесно зберіг клієнтів без замовлень, доповнивши їхній o.status значенням NULL. Але потім WHERE o.status = 'paid' перевіряє цей NULL — і NULL = 'paid' дає не «істина» і не «хиба», а «невідомо», тож рядок відсіюється. У результаті всі клієнти без оплачених замовлень зникли, і LEFT JOIN тихо перетворився на INNER JOIN. У другому запиті умова стоїть в ON: вона впливає на те, які замовлення прикріпляться під час зʼєднання, але не викидає клієнтів — хто без оплачених замовлень, той лишається з NULL у стовпці замовлення.

    Правило: фільтр на праву (nullable) таблицю LEFT JOIN кладуть в ON, а не у WHERE — інакше зникає сам сенс outer-джойна. Виняток свідомий: якщо WHERE right.key IS NULL — це вже навмисний анти-джойн, і саме там WHERE доречний.

    JOIN трьох і більше таблиць

    Реальні запити рідко обмежуються двома таблицями. Щоб зібрати «клієнт, місто, замовлення, товар», джойни ставлять ланцюгом — кожен наступний приєднує ще одну таблицю до вже зʼєднаного набору:

    SELECT c.name, ci.name AS city, o.id AS order_id, p.name AS product
FROM orders AS o
JOIN customers   AS c  ON c.id = o.customer_id
JOIN cities      AS ci ON ci.id = c.city_id
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products    AS p  ON p.id = oi.product_id;

    СУБД обчислює це послідовно, але оптимізатор вільний змінювати фізичний порядок зʼєднань — на результат це не впливає, лише на швидкодію (тема глави Індекси і швидкодія поки що попереду). Що справді впливає на результат — це тип кожного джойна в ланцюгу. Варто памʼятати одну річ: щойно ви зробили LEFT JOIN, а наступним кроком INNER JOIN до тієї самої, потенційно NULL-ової таблиці, — INNER викине рядки з NULL, і ваш LEFT знеціниться. Якщо треба зберегти рядки лівого боку до кінця ланцюга, усі наступні джойни по цій гілці теж мають бути LEFT.

    Побіжно про синоніми: USING (customer_id) — це скорочення для ON a.customer_id = b.customer_id, коли стовпці однойменні (об'єднує їх в один у виводі). А от NATURAL JOIN, який сам зʼєднує за всіма однойменними стовпцями, у продакшн-запитах краще не застосовувати — досить комусь додати таблиці ще один випадково однойменний стовпець (наприклад, created_at), і умова зʼєднання тихо зміниться. Явний ON завжди надійніший.

    Типові помилки

    • LEFT JOIN, а рядків стало менше, ніж у лівій таблиці. Виглядає як загублені дані, а насправді умову на праву таблицю поклали у WHERE замість ON — і outer-джойн виродився в inner.
    • SUM/COUNT після джойна завищені в кілька разів. Виглядає як дублікати в даних, а насправді звʼязок 1:N розмножив рядки «одиничної» таблиці, і агрегат порахував їх повторно.
    • Рядки з NULL у ключі зникли після INNER JOIN. Виглядає як втрата записів, а насправді NULL не дорівнює нічому, навіть іншому NULL, тож пари для нього не знаходиться ніколи.
    • FULL OUTER JOIN падає з синтаксичною помилкою. Виглядає як помилка у вашому SQL, а насправді це MySQL, який FULL OUTER JOIN не підтримує — потрібна емуляція через UNION.
    • Таблиці через кому — і рядків раптом десятки тисяч. Виглядає як звичайний джойн, а насправді забута умова зʼєднання перетворила його на CROSS JOIN з декартовим добутком.
    • NATURAL JOIN повертає не те. Виглядає як зручне скорочення, а насправді він зʼєднав за випадково однойменним стовпцем, про який ви не подумали.

    Підсумок

    • INNER JOIN лишає тільки збіги; LEFT/RIGHT/FULL OUTER зберігають рядки без пари, доповнюючи відсутню сторону NULL.
    • Фільтр на праву таблицю в LEFT JOIN кладуть у ON; той самий фільтр у WHERE мовчки перетворює його на INNER JOIN.
    • Зʼєднання за звʼязком 1:N розмножує рядки «одиничного» боку — будь-який COUNT/SUM після такого джойна перевіряй окремо.
    • Сирітські записи й записи без пари шукають анти-джойном: LEFT JOIN ... WHERE right_table.key IS NULL.
    • У ланцюгу кількох таблиць тип кожного джойна важить: INNER після LEFT по nullable-гілці знецінює LEFT.

    Що питають на співбесіді

    • «Чим INNER JOIN відрізняється від LEFT JOIN?» — базове питання-фільтр. Інтервʼюер хоче почути не лише «LEFT зберігає ліву таблицю», а й звідки беруться NULL у результаті та які рядки зникають при INNER.
    • «Знайдіть клієнтів, які не зробили жодного замовлення». Очікують анти-джойн (LEFT JOIN ... WHERE o.id IS NULL) або еквівалент через NOT EXISTS; слабка відповідь — спроба через NOT IN, який ламається на NULL.
    • «Після JOIN сума виторгу подвоїлась — чому?» — перевірка розуміння 1:N. Дивляться, чи згадаєте розмноження рядків і чи запропонуєте агрегувати до джойна або COUNT(DISTINCT).
    • «У чому різниця між умовою в ON і у WHERE для LEFT JOIN?» — сеньйорський сигнал. Сильний кандидат пояснює логічний порядок обробки: ON діє під час зʼєднання, WHERE — після, коли NULL-рядки вже додані.
    • «Що таке CROSS JOIN і коли він трапляється випадково?» — перевіряють, чи розумієте декартів добуток і звідки беруться роздуті результати.

    Головне, на що дивиться інтервʼюер, — чи міркуєте ви про які рядки лишаються і звідки береться NULL, а не переказуєте завчені означення типів джойна. Уміння накидати анти-джойн і пояснити пастку 1:N цінується вище за знання, що RIGHT JOIN існує.

    Джерела

    • PostgreSQL: Table Expressions — Joined Tables — точна семантика INNER/LEFT/RIGHT/FULL/CROSS, ON/USING/NATURAL.
    • MySQL: JOIN clause — синтаксис джойнів у MySQL і підтверджена відсутність FULL OUTER JOIN.
    • ISO/IEC 9075 (SQL) — міжнародний стандарт мови SQL, де формально визначено операції зʼєднання; діалекти СУБД відхиляються від нього в деталях.

    Примітка: SQL і робота з БД не входять до силабусу ISTQB CTFL 4.0 — це прикладна навичка тестувальника, тож канонічні джерела тут — документація СУБД і стандарт мови, а не силабус.