SQL JOIN простими словами: INNER, LEFT, RIGHT, FULL і CROSS JOIN з прикладами

JOIN — це спосіб зібрати в одному запиті дані з кількох таблиць: клієнтів із їхніми замовленнями, товари з категоріями, працівників з керівниками. Це одна з найважливіших тем SQL, на якій регулярно «сиплються» і на співбесідах, і в реальній роботі: зайвий WHERE непомітно перетворює LEFT JOIN на INNER, а суми подвоюються.

Розберемо всі типи JOIN на двох маленьких таблицях, з готовим результатом для кожного запиту. Приклади працюють у PostgreSQL, і майже всі — у MySQL, SQLite та SQL Server.

Дані для прикладів

Дві таблиці: користувачі й замовлення. Зверніть увагу на дві «незручні» деталі — на них і тримаються всі відмінності між JOIN:

• Ірина ще нічого не замовляла;
• замовлення 104 не прив'язане до користувача (наприклад, гостьова покупка): user_id порожній.

users

orders

Щоб повторювати приклади, створіть таблиці в будь-якій базі:

CREATE TABLE users  (id int PRIMARY KEY, name text NOT NULL, city text);
CREATE TABLE orders (id int PRIMARY KEY, user_id int REFERENCES users(id), amount numeric NOT NULL);

INSERT INTO users  VALUES (1, 'Олена', 'Київ'), (2, 'Тарас', 'Львів'), (3, 'Ірина', 'Одеса');
INSERT INTO orders VALUES (101, 1, 1200), (102, 1, 350), (103, 2, 800), (104, NULL, 150);

Як читати JOIN

SELECT ...
FROM users u                      -- ліва таблиця
JOIN orders o ON o.user_id = u.id -- права таблиця й умова зв'язку

u і o — короткі псевдоніми таблиць. Умова ON каже, які рядки вважаються парою: замовлення належить користувачу, якщо o.user_id = u.id. А тип JOIN визначає, що робити з рядками, яким пари не знайшлося.

INNER JOIN: лише пари

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

SELECT u.name, o.id AS order_id, o.amount
FROM users u
JOIN orders o ON o.user_id = u.id;

Ірини немає, бо в неї немає замовлень. Замовлення 104 немає, бо в нього немає користувача. І зверніть увагу: Олена з'явилася двічі — по разу на кожне замовлення. JOIN не «додає колонки до таблиці», він будує всі пари рядків, що підходять під умову.

LEFT JOIN: усі зліва, навіть без пари

Повертає всі рядки лівої таблиці. Де пари немає, колонки правої таблиці заповнюються NULL.

SELECT u.name, o.id AS order_id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Це найуживаніший тип JOIN після INNER: «покажи всіх клієнтів і їхні замовлення, якщо вони є». LEFT JOIN і LEFT OUTER JOIN — одне й те саме.

RIGHT JOIN: усі справа

Дзеркальний до LEFT: зберігає всі рядки правої таблиці.

SELECT u.name, o.id AS order_id, o.amount
FROM users u
RIGHT JOIN orders o ON o.user_id = u.id;

На практиці RIGHT JOIN пишуть рідко: той самий результат дає LEFT JOIN, якщо поміняти таблиці місцями (FROM orders o LEFT JOIN users u ...). Такі запити легше читати — головна таблиця завжди зліва.

FULL JOIN: усі з обох боків

Зберігає всі рядки обох таблиць: пари, ліві без пари й праві без пари.

SELECT u.name, o.id AS order_id, o.amount
FROM users u
FULL JOIN orders o ON o.user_id = u.id;

FULL JOIN корисний для звіряння двох наборів даних: що є лише в першому джерелі, що лише в другому, а що в обох.

FULL JOIN у MySQL

MySQL не підтримує FULL JOIN. Його емулюють об'єднанням LEFT JOIN і «правих сиріт»:

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

CROSS JOIN: кожен з кожним

Декартовий добуток: кожен рядок лівої таблиці поєднується з кожним рядком правої, без умови. 3 користувачі × 4 замовлення = 12 рядків.

SELECT u.name, o.id
FROM users u
CROSS JOIN orders o;

Звучить безглуздо, але має реальні застосування: згенерувати всі комбінації розмірів і кольорів товару або побудувати «сітку» дат × магазинів для звіту, щоб дні без продажів теж потрапили в результат.

Випадковий CROSS JOIN

Якщо забути умову зв'язку в старому синтаксисі (FROM users, orders без WHERE), ви отримаєте саме декартовий добуток. На таблицях з мільйонами рядків такий запит здатен покласти базу. Пишіть явні JOIN ... ON — так забуту умову видно одразу.

Self join: таблиця сама з собою

Буває, що зв'язок усередині однієї таблиці: у працівника є керівник, який теж працівник.

Беремо ту саму таблицю двічі з різними псевдонімами:

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

LEFT JOIN тут потрібен, щоб не загубити Марину: у неї керівника немає.

Як знайти рядки без пари (anti-join)

Класична задача: «клієнти, які нічого не замовляли». Перший спосіб — LEFT JOIN і перевірка на NULL:

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

Другий, часто зрозуміліший — NOT EXISTS:

SELECT u.name
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Обидва запити повернуть Ірину, і PostgreSQL виконує їх однаково ефективно.

Пастка з NOT IN і NULL

Здавалося б, можна написати так:

SELECT name FROM users WHERE id NOT IN (SELECT user_id FROM orders);

На наших даних запит поверне порожній результат, хоча Ірина нічого не замовляла. Причина — замовлення 104 з user_id = NULL. Порівняння з NULL дає «невідомо», і вся умова NOT IN перестає бути істинною для будь-якого рядка. Для пошуку відсутніх записів використовуйте NOT EXISTS.

Три найчастіші пастки з JOIN

1. Умова в WHERE «вбиває» LEFT JOIN

Хочемо всіх користувачів і лише великі замовлення (понад 500):

-- Неправильно
SELECT u.name, o.id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.amount > 500;

Ірина зникла. WHERE виконується після JOIN, і для Ірини o.amount дорівнює NULL, а NULL > 500 — не істина. Фактично вийшов INNER JOIN. Умову на праву таблицю треба переносити в ON:

-- Правильно
SELECT u.name, o.id, o.amount
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.amount > 500;

Правило просте: у LEFT JOIN умови на праву таблицю пишіть в ON, а умови на ліву — у WHERE.

2. COUNT(*) рахує порожні рядки

Скільки замовлень у кожного користувача?

SELECT u.name,
       COUNT(*)                    AS wrong_count,
       COUNT(o.id)                 AS orders_count,
       COALESCE(SUM(o.amount), 0)  AS total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.name;

COUNT(*) рахує рядки, а LEFT JOIN залишив для Ірини один рядок із NULL, тож вийшло «одне замовлення». COUNT(o.id) рахує лише не порожні значення — і показує правду. COALESCE замінює NULL у сумі на нуль.

3. Дублювання при кількох JOIN

Додамо таблицю звернень у підтримку: в Олени їх три. Рахуємо суму замовлень і кількість звернень одним запитом:

-- Неправильно
SELECT u.name, SUM(o.amount) AS total, COUNT(t.id) AS tickets
FROM users u
JOIN orders  o ON o.user_id = u.id
JOIN tickets t ON t.user_id = u.id
GROUP BY u.name;

Для Олени запит поверне суму 4650 замість 1550 і 6 звернень замість 3. Два замовлення × три звернення = шість рядків, і кожне замовлення пораховано тричі. Це називається «розмноження рядків» (fan-out), і воно непомітно псує звіти.

Рішення — спершу агрегувати кожну таблицю окремо, а потім з'єднувати вже підсумки:

WITH o AS (SELECT user_id, SUM(amount) AS total FROM orders  GROUP BY user_id),
     t AS (SELECT user_id, COUNT(*)    AS tickets FROM tickets GROUP BY user_id)
SELECT u.name,
       COALESCE(o.total, 0)   AS total,
       COALESCE(t.tickets, 0) AS tickets
FROM users u
LEFT JOIN o ON o.user_id = u.id
LEFT JOIN t ON t.user_id = u.id;

Перевірка на розмноження

Якщо підозрюєте дублювання, порівняйте COUNT(*) до і після JOIN. Якщо після JOIN рядків стало більше, ніж ви очікували, — десь зв'язок «один до багатьох», і агрегати треба рахувати окремо.

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

JOIN-и просто йдуть один за одним. Наприклад, позиції замовлення з назвами товарів:

SELECT o.id AS order_id, p.title, oi.quantity, oi.price
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
JOIN products    p  ON p.id = oi.product_id
WHERE o.id = 101;

Якщо колонки зв'язку називаються однаково в обох таблицях, можна скоротити запис через USING: JOIN order_items USING (order_id). А от NATURAL JOIN, який сам шукає однакові назви колонок, краще не використовувати: варто додати в таблицю колонку created_at чи name — і запит мовчки почне з'єднувати ще й за нею.

JOIN і продуктивність

• Індексуйте колонки зв'язку. PostgreSQL автоматично створює індекси для первинних ключів, але не для зовнішніх ключів. Для orders.user_id індекс треба створити самостійно:

CREATE INDEX orders_user_id_idx ON orders (user_id);

• Дивіться план запиту. EXPLAIN ANALYZE покаже, як база з'єднує таблиці: Nested Loop (добре для невеликих вибірок з індексом), Hash Join (великі таблиці без індексу) чи Merge Join (обидві сторони відсортовані).
• Фільтруйте якомога раніше. Умови в WHERE на ліву таблицю зменшують кількість рядків, які взагалі доведеться з'єднувати.
• Не тягніть SELECT *. Особливо з кількома JOIN: однакові назви колонок (id, name) з різних таблиць плутаються, а зайві дані передаються мережею.

Шпаргалка

Часті питання

Чим JOIN відрізняється від UNION? JOIN з'єднує таблиці «вшир» — додає колонки з іншої таблиці. UNION з'єднує результати «вдовж» — дописує рядки одного запиту під рядки іншого з тим самим набором колонок.

Що швидше: JOIN чи підзапит? У сучасних базах (PostgreSQL, MySQL 8+) оптимізатор часто перетворює одне на інше, і план виходить однаковий. Пишіть так, як зрозуміліше, а сумніви перевіряйте через EXPLAIN ANALYZE.

Чи важливий порядок таблиць в INNER JOIN? На результат — ні: A JOIN B і B JOIN A повертають ті самі рядки. Порядок з'єднання обирає оптимізатор. А от для LEFT і RIGHT JOIN порядок принциповий.

Чому в результаті з'явилися дублікати? Майже завжди причина — зв'язок «один до багатьох»: на один рядок зліва припадає кілька рядків справа. Це не помилка JOIN, а його природа. Якщо потрібні унікальні рядки — агрегуйте (GROUP BY) або перевірте, чи правильна умова в ON.

Що далі

Опанувавши JOIN, переходьте до віконних функцій — вони розв'язують задачі на кшталт «топ-3 замовлення кожного клієнта» без самоз'єднань. Статті про це — в рубриках SQL і PostgreSQL.