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

Навіщо взагалі потрібен JOIN — чому не тримати всі дані в одній таблиці?

Одна велика таблиця означала б дублювання: імʼя клієнта повторюється в кожному його замовленні, і зміна імені вимагає оновити тисячі рядків з ризиком розсинхрону. Тому реляційна модель зберігає кожен факт один раз, а між таблицями лишає посилання — замовлення знає тільки customer_id. Ціна такого дизайну: повної картини немає в жодній таблиці окремо, і щоб показати «замовлення № 101 клієнта Ігоря з Києва», розкидані шматки доводиться збирати запитом. Інструмент цього збирання — JOIN: він зіставляє рядки таблиць за умовою відповідності. QA користується ним щодня — звірити, чи UI зшив дані правильно, і виловити записи, що посилаються в нікуди.

Що робить INNER JOIN і які рядки він відсіює?

INNER JOIN (те саме, що просто JOIN) повертає лише ті комбінації рядків двох таблиць, для яких умова ON дала істину, — фактично перетин. Усе, що пари не знайшло, відсіюється, причому з обох боків одразу. Практичний наслідок, який часто забувають: замовлення з customer_id, відсутнім у customers, випаде з вибірки без жодного попередження — і клієнт, який нічого не купував, теж. Тож INNER JOIN працює одночасно і як зʼєднання, і як мовчазний фільтр.

Чим INNER JOIN відрізняється від LEFT JOIN?

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

Звідки в результаті LEFT JOIN беруться NULL?

Ці NULL кажуть «відповідності справа не існує» — це не число нуль і не порожній рядок. Механіка проста: LEFT JOIN зобовʼязаний віддати рядок лівої таблиці навіть без пари, але стовпці правої таблиці мусить чимось зайняти — і єдиний чесний варіант тут NULL. Тому клієнт без замовлень приходить одним рядком з NULL в order_id. Це прямий місток до пасток NULL: такий рядок не можна фільтрувати звичайним порівнянням, бо NULL = щось дає не «істина» і не «хиба», а «невідомо». Розуміння, що NULL тут — маркер відсутньої пари, а не значення, відрізняє того, хто розуміє механіку, від того, хто завчив означення.

Навіщо потрібен RIGHT JOIN, якщо є LEFT?

RIGHT JOIN — дзеркальна версія LEFT: виживають усі рядки правої таблиці, а ліву сторону там, де пари немає, займає NULL. У реальних запитах він майже не трапляється: досить поміняти таблиці місцями — і той самий результат дає LEFT JOIN, а коли всі джойни в запиті ліві, оку не треба перемикати напрямок читання. Тому знати RIGHT треба, щоб зрозуміти чужий код, а застосовувати у власному — здебільшого ні. Це радше питання на розуміння, ніж на щоденну практику: сильний кандидат згадає, що RIGHT існує, але одразу додасть, що звів би все до LEFT.

Як знайти клієнтів, які не зробили жодного замовлення?

Це канонічне питання, і очікують від нього анти-джойн (anti-join). Ідея: LEFT JOIN чесно зберігає всіх клієнтів, а фільтр після нього відбирає лише рядки, де замість замовлення стоїть 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;

Еквівалент — через NOT EXISTS. Слабка відповідь — спроба через NOT IN, який ламається на NULL у підзапиті. Тобто тут одночасно потрібні дві речі: знання outer-джойна і розуміння поведінки NULL.

Що таке сирітські записи і як їх шукають джойном?

Сирітський (orphan) запис — це рядок, що посилається зовнішнім ключем на неіснуючий рядок іншої таблиці: замовлення з customer_id, якого в customers уже немає. Шукають їх тим самим анти-джойном — LEFT JOIN плюс WHERE на NULL з боку батьківської таблиці:

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 це готова перевірка посилальної цілісності (referential integrity): коли зовнішні ключі налаштовані правильно, відповідь буде порожньою. Кожен рядок у відповіді — баг даних і прямий кандидат у дефект, а не косметика.

Чому NOT IN небезпечний для пошуку записів без пари?

NOT IN тихо ламається, коли в списку значень трапляється NULL. Механізм такий: x NOT IN (1, 2, NULL) розкривається у x <> 1 AND x <> 2 AND x <> NULL, а останнє порівняння дає «невідомо», через що весь вираз ніколи не стає істинним — і запит повертає порожньо, хоча даних насправді повно. Тому для «знайди тих, у кого немає пари» надійніші анти-джойн (LEFT JOIN ... WHERE key IS NULL) або NOT EXISTS, які коректно поводяться з NULL. Пастка тиха: запит виглядає правильним, а повертає не те й без жодної помилки. Розуміння цієї межі — ознака, що людина реально працювала з брудними даними.

Що таке CROSS JOIN і коли декартів добуток виникає випадково?

CROSS JOIN стоїть окремо від решти: у нього немає ON. Він повертає декартів добуток (Cartesian product) — усі можливі комбінації рядків, тож дві таблиці по 10 рядків дадуть 100. Зрідка це задумано — наприклад, згенерувати всі пари «розмір × колір». Типовіший сценарій — випадковий: старий синтаксис перелічення таблиць через кому (FROM orders o, customers c), де умову зʼєднання мали написати у WHERE, але забули, — і СУБД слухняно перемножила таблиці. Вибірка розростається на порядки, запит гальмує, цифри у звіті втрачають сенс. Явний JOIN ... ON захищає краще: PostgreSQL відхилить INNER JOIN без умови як синтаксичну помилку. Щоправда, MySQL пропустить — там JOIN, INNER JOIN і CROSS JOIN взаємозамінні, тож пильність потрібна все одно.

Що робить FULL OUTER JOIN і чому запит із ним може несподівано впасти?

FULL OUTER JOIN поєднує поведінку LEFT і RIGHT одночасно: непарні рядки виживають з обох боків, а бракуючу половину кожного такого рядка займає NULL. Найкорисніший сценарій — звірка двох наборів даних: одразу видно, що існує лише зліва, що лише справа, а що збіглося. Пастка тут діалектна: у PostgreSQL він працює й описаний у довідці з табличних виразів, а MySQL його не має досі — підтримка там лишається невиконаною задачею (worklog WL#1604 зі станом Un-Assigned і відкритий feature request #18003 «FULL OUTER JOIN (no syntax to cover)»); сама сторінка JOIN Clause у довіднику MySQL про нього не пише нічого. Тому синтаксична помилка на FULL OUTER JOIN — привід спитати не «що не так з моїм SQL», а «яка СУБД під запитом»; на MySQL той самий результат складають з лівого і правого джойнів через UNION. І одразу друге дно: UNION без ALL схлопує не лише дублікати від збігів, а й справжні однакові рядки таблиці, тож емуляція може повернути менше рядків, ніж навіть простий LEFT JOIN, — на звірці наборів це тихо ховає частину розбіжностей.

Що таке self-join і навіщо він потрібен?

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

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 застосовують і для ієрархій категорій, і щоб знайти пари рядків з однаковим значенням.

Після JOIN сума виторгу подвоїлась — чому і як це полагодити?

Це перевірка розуміння звʼязку «один до багатьох» (1:N). Джойн повертає рядок на кожну знайдену пару, тому сторона «один» у результаті дублюється: клієнт із трьома замовленнями зʼявиться тричі. Це коректна поведінка, а не дефект — рівно доти, доки поверх результату не зʼявляється агрегат. Щойно після зʼєднання orders з order_items рахують SUM(o.total), кожен total потрапляє в суму по разу на позицію замовлення — і виторг завищується. Впізнати проблему легко: цифра відрізняється від очікуваної в ціле число разів, а не хаотично. Лікування залежить від задачі — агрегувати «множинну» таблицю окремо (у підзапиті чи CTE) до зʼєднання або застосувати COUNT(DISTINCT ...) замість COUNT(*).

У чому різниця між умовою в ON і у WHERE для LEFT JOIN?

Це сеньйорський сигнал. Для INNER JOIN різниці в результаті немає, а для LEFT JOIN вона фундаментальна. Розгадка в тому, коли що обчислюється: етап ON формує зʼєднаний набір — у outer-джойна саме тут народжуються NULL-рядки для лівих рядків без пари, — а WHERE фільтрує вже готовий набір. Фільтр на праву таблицю у WHERE (скажімо, WHERE o.status = 'paid') наткнеться на той свіжий NULL, порівняння дасть «невідомо», рядок не пройде — і від LEFT лишиться поведінка INNER: клієнти без оплачених замовлень випаруються. Той самий фільтр в ON (ON o.customer_id = c.id AND o.status = 'paid') впливає лише на те, які замовлення прикріпляться, але клієнтів не викидає. Правило: фільтр на праву (nullable) таблицю LEFT JOIN кладуть в ON, а не у WHERE.

Який логічний порядок обробки джойна і чому він важливий?

СУБД обробляє запит не в тому порядку, у якому він написаний: спершу FROM і JOIN ... ON формують зʼєднаний набір, потім WHERE фільтрує його, далі йдуть GROUP BY, HAVING, SELECT і ORDER BY. Ключове для джойнів — що outer-джойн додає свої NULL-рядки на етапі ON, а WHERE бачить їх уже готовими. Саме тому одна й та сама умова в ON і у WHERE дає різний результат для LEFT JOIN: перша діє під час зʼєднання, друга — після, коли NULL-рядки вже в наборі. Оптимізатор може переставляти фізичний порядок зʼєднань як завгодно — це лише питання швидкодії; що саме повернеться, визначають логічний порядок обробки і тип кожного джойна.

У ланцюгу з кількох таблиць чому INNER після LEFT може «знецінити» LEFT?

Реальні запити рідко обмежуються двома таблицями — джойни ставлять ланцюгом, кожен наступний приєднує ще одну таблицю до вже зʼєднаного набору. Тип кожного джойна в ланцюгу важить окремо. Пастка виглядає так: перший крок — LEFT JOIN до гілки, де пари може не бути; другий — INNER JOIN, що чіпляється до тієї самої гілки. У рядків без пари ключ гілки — NULL, умова наступного INNER на ньому не спрацює, і все, що LEFT дбайливо зберіг, вилетить на цьому кроці. Тому якщо треба зберегти рядки лівого боку до кінця ланцюга, усі наступні джойни по цій гілці теж мають бути LEFT. Це типова причина «загублених» рядків у складному звіті, де перший джойн начебто правильний.

Чим USING відрізняється від ON і чому NATURAL JOIN краще не застосовувати?

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

Три кейси, де вибір джойна вирішує, чи звіт каже правду: таблиця рішень «який JOIN під яку задачу», діагностика роздутого виторгу на звʼязку 1:N і анти-джойн як перевірка посилальної цілісності, яку можна ганяти прямо з автотесту. Скрізь — що дивитися і чому.

Кейс 1. Таблиця рішень: який JOIN обрати під задачу

Найчастіша помилка новачка — узяти INNER JOIN там, де потрібен LEFT, і мовчки загубити рядки. Питання, на яке треба відповісти перед вибором: чи потрібні рядки лівої таблиці, для яких немає пари справа? Якщо так — outer-джойн, якщо ні — inner.

Розбір, чому саме так:

• LEFT замість INNER — коли важливі «нулі». «Скільки замовлень у кожного клієнта» через INNER JOIN тихо викине клієнтів з нулем замовлень, і звіт покаже завищене середнє. LEFT JOIN збереже їх з NULL, який COUNT(o.id) чесно порахує як нуль (а от COUNT(*) дав би 1 — він рахує рядки, не значення).
• FULL OUTER — інструмент звірки, а не щоденний джойн. Його беруть, коли треба побачити розбіжність двох джерел одразу з обох боків. Памʼятайте про діалекти: у PostgreSQL він є, у MySQL його немає (WL#1604 Un-Assigned, feature request #18003 відкритий), і там складають LEFT та RIGHT через UNION. Саме на звірці ця емуляція й підводить: UNION без ALL схлопує й справжні дублікати рядків, тож частина розбіжностей, заради яких ви й брали FULL OUTER, зникне мовчки.
• CROSS JOIN пишуть явно рідко. Якщо він зʼявився у вас незапланованим — швидше за все, ви перелічили таблиці через кому й забули умову у WHERE.

Кейс 2. Виторг у звіті подвоївся: діагностика пастки 1:N

Аналітик скаржиться: сумарний виторг по клієнтах у новому звіті помітно більший, ніж у старому, хоча замовлень не додавалось. Запит виглядає невинно:

-- НЕБЕЗПЕЧНО: total подвоюється на кожну позицію замовлення
SELECT c.name, SUM(o.total) AS revenue
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;

Симптом-підказка: цифра завищена рівно в кілька разів, не хаотично. Це підпис звʼязку «один до багатьох». Джойн orders з order_items розмножив кожне замовлення стільки разів, скільки в ньому позицій, а SUM(o.total) слухняно додав total за кожен дубль. Замовлення на 500 грн з трьома позиціями внесло у виторг 1500.

Як довести діагноз за 10 секунд — прибрати агрегат і подивитись на сирі рядки:

SELECT c.name, o.id AS order_id, o.total, oi.id AS item_id
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
WHERE c.name = 'Ігор';

Якщо один order_id повторюється трьома рядками з різними item_id, а total у всіх однаковий — діагноз підтверджено: SUM рахує по роздутому набору.

Лікування — агрегувати «множинну» таблицю до зʼєднання, щоб на один рядок замовлення лишався один total:

SELECT c.name, SUM(o.total) AS revenue
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.name;

Тут order_items узагалі не потрібна для суми виторгу — її приєднання й було зайвим. Якщо ж позиції справді потрібні (наприклад, порахувати їх кількість), рахують COUNT(DISTINCT oi.id) і SUM по заздалегідь згорнутій у підзапиті сумі, а не наосліп поверх джойна. Мораль для QA: будь-який COUNT/SUM після джойна перевіряй окремо — «завищено в N разів» майже завжди означає 1:N.

Кейс 3. Анти-джойн як перевірка цілісності — прямо з автотесту

Посилальну цілісність (referential integrity) не обовʼязково перевіряти очима. Якщо база доступна тесту, анти-джойн стає оракулом: у здоровій базі сирітських записів немає, тож запит має повернути нуль рядків. Будь-який рядок у відповіді — це баг даних.

-- сирітські замовлення: 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;

Механіка та сама, що й у пошуку «клієнтів без замовлень», тільки перевернута: LEFT JOIN зберігає всі замовлення, а WHERE c.id IS NULL лишає рівно ті, для яких клієнта не знайшлося. Загорнути це в перевірку можна навіть без браузера — через прямий запит до БД у Playwright-тесті:

import { test, expect } from '@playwright/test';
import { Client } from 'pg';

test('немає сирітських замовлень: кожен customer_id існує', async () => {
  const db = new Client({ connectionString: process.env.DATABASE_URL });
  await db.connect();

  const { rows } = await db.query(`
    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
  `);

  // здорова база: сирітських записів немає, отже нуль рядків
  expect(rows, `знайдено сирітські замовлення: ${JSON.stringify(rows)}`).toHaveLength(0);
  await db.end();
});

Що дивитися і чому:

• Порожній результат — це зелений тест, не помилка. Тут «нуль рядків» і є очікуваний стан. Асерт toHaveLength(0) з описом у повідомленні одразу покаже, які саме id осиротіли, якщо перевірка почервоніє.
• Не плутайте анти-джойн з фільтром, що зіпсував LEFT. Тут WHERE ... IS NULL — навмисний, у цьому весь сенс. А ось WHERE c.name = 'X' на nullable-боці мовчки виродив би LEFT в INNER і зламав перевірку. Різницю дає той самий логічний порядок «спершу ON, потім WHERE».
• Ту саму ідею масштабують на будь-який зовнішній ключ. order_items → orders, orders → customers, products → categories: один шаблон анти-джойна покриває всі звʼязки. Це дешевий і надійний набір санітарних перевірок даних після міграцій.

Типи джойнів: що лишається в результаті

• Розумію, навіщо JOIN узагалі потрібен: дані навмисно розкидані по таблицях (замовлення тримає лише customer_id), і жодна окрема таблиця не показує повної картини.
• Знаю, що INNER JOIN і просто JOIN — синоніми, і що він лишає тільки перетин: пари, де умова ON істинна, а рядки без пари з будь-якого боку зникають мовчки.
• Можу пояснити, що LEFT JOIN гарантує кожен рядок лівої таблиці в результаті, а стовпці правої заповнює NULL, коли пари немає.
• Розумію, що RIGHT JOIN — дзеркало LEFT, і чому на практиці його зводять до LEFT перестановкою таблиць.
• Знаю, що FULL OUTER JOIN зберігає рядки без пари з обох боків, що в PostgreSQL він є, а в MySQL його немає — там це досі невиконана задача (WL#1604 Un-Assigned, відкритий feature request #18003) і працюють через емуляцію UNION.
• Пам'ятаю, що емуляція LEFT UNION RIGHT не точний еквівалент: UNION без ALL схлопує й справжні дублікати рядків, тож на звірці наборів частина розбіжностей губиться мовчки.
• Розумію, що CROSS JOIN не має ON і дає декартів добуток — кожен рядок з кожним.

NULL і анти-джойн

• Розумію, що NULL після outer-джойна означає «пари не знайшлося», а не «нуль» чи «порожньо».
• Можу написати анти-джойн для «знайди записи без пари»: LEFT JOIN ... WHERE right_table.key IS NULL.
• Знаю, що цей самий патерн ловить сирітські (orphan) записи й бите посилання зовнішнього ключа — готова перевірка посилальної цілісності для QA.
• Розумію, чому NOT IN небезпечний на NULL у підзапиті (весь вираз стає «невідомо» і повертає порожньо), і що надійніші NOT EXISTS або анти-джойн.

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

• Знаю, що для INNER JOIN різниці між умовою в ON і у WHERE немає, а для LEFT JOIN вона фундаментальна.
• Можу пояснити логічний порядок: спершу ON (тут outer-джойн додає NULL-рядки), потім WHERE по всьому набору.
• Розумію, чому фільтр на праву таблицю у WHERE (WHERE o.status = 'paid') тихо перетворює LEFT на INNER, а той самий фільтр в ON — ні.
• Памʼятаю виняток: WHERE right.key IS NULL — це навмисний анти-джойн, і там WHERE доречний.

Дублікати 1:N і агрегати

• Розумію, що зʼєднання за звʼязком «один до багатьох» (1:N) розмножує рядки «одиничного» боку — це не баг сам по собі.
• Можу пояснити, чому SUM/COUNT(*) після такого джойна завищені, і впізнаю симптом: результат більший за очікуваний рівно в кілька разів.
• Знаю способи лікування: агрегувати «множинну» таблицю окремо в підзапиті/CTE до джойна або застосувати COUNT(DISTINCT ...).

Кілька таблиць, self-join і синтаксис

• Розумію self-join: таблиця зʼєднана сама із собою під різними аліасами (працівник → керівник), і чому тут беруть LEFT, щоб не втратити верхівку з manager_id IS NULL.
• Знаю, що в ланцюгу джойнів INNER після LEFT по nullable-гілці знецінює LEFT, і що для збереження лівих рядків усі наступні джойни гілки теж мають бути LEFT.
• Розумію, що USING (col) — безпечне скорочення для ON a.col = b.col, а NATURAL JOIN крихкий: тихо зʼєднає за будь-яким випадково однойменним стовпцем.