Пошук уроків, статей та іншого контенту
Закріпите зв’язки й об’єднання таблиць, розв’язуючи прикладні задачі від простих до комплексних.
У практичних задачах JOIN важливо не лише правильно поєднати таблиці, а й:
не втратити рядки через неправильний тип об’єднання;
врахувати записи, для яких немає відповідностей;
не створити зайві дублікати;
правильно фільтрувати дані до або після об’єднання;
поєднати JOIN з агрегатними функціями та віконними функціями.
Усі приклади нижче використовують PostgreSQL.
Створимо невелику базу даних інтернет-магазину:
customers — клієнти;
orders — замовлення;
order_items — позиції замовлень;
products — товари;
categories — категорії товарів.
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id integer PRIMARY KEY,
full_name text NOT NULL,
city text NOT NULL
);
CREATE TABLE categories (
id integer PRIMARY KEY,
name text NOT NULL UNIQUE
);
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
category_id integer NOT NULL REFERENCES categories(id),
price numeric(10, 2) NOT NULL CHECK (price >= 0)
);
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers(id),
order_date date NOT NULL,
status text NOT NULL CHECK (status IN ('pending', 'completed', 'cancelled'))
);
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders(id),
product_id integer NOT NULL REFERENCES products(id),
quantity integer NOT NULL CHECK (quantity > 0),
unit_price numeric(10, 2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers (id, full_name, city) VALUES
(1, 'Анна Коваль', 'Київ'),
(2, 'Богдан Мельник', 'Київ'),
(3, 'Олена Шевченко', 'Одеса'),
(4, 'Дмитро Бондар', 'Львів');
INSERT INTO categories (id, name) VALUES
(1, 'Електроніка'),
(2, 'Книги'),
(3, 'Дім');
INSERT INTO products (id, name, category_id, price) VALUES
(11, 'Ноутбук', 1, 1000.00),
(12, 'Миша', 1, 25.00),
(13, 'Книга PostgreSQL', 2, 40.00),
(14, 'Офісне крісло', 3, 150.00),
(15, 'Настільна лампа', 3, 30.00),
(16, 'Принтер', 1, 220.00);
INSERT INTO orders (id, customer_id, order_date, status) VALUES
(101, 1, '2025-01-10', 'completed'),
(102, 1, '2025-02-15', 'completed'),
(103, 2, '2025-02-20', 'cancelled'),
(104, 3, '2025-03-01', 'completed'),
(105, 4, '2025-03-03', 'pending'),
(106, 2, '2025-03-05', 'completed');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(101, 11, 1, 1000.00),
(101, 12, 2, 25.00),
(102, 13, 2, 40.00),
(103, 14, 1, 150.00),
(104, 14, 2, 150.00),
(104, 15, 1, 30.00),
(105, 12, 1, 25.00),
(106, 13, 1, 40.00),
(106, 12, 1, 25.00);У прикладах виручка обчислюється за order_items.unit_price, а не за поточною ціною товару в products. Це важливо: ціна товару могла змінитися після створення замовлення.
Потрібно вивести:
ідентифікатор замовлення;
дату;
статус;
ім’я клієнта;
місто клієнта.
Оскільки нам потрібні лише замовлення, які мають відповідного клієнта, використаємо INNER JOIN.
SELECT
o.id AS order_id,
o.order_date,
o.status,
c.full_name,
c.city
FROM orders AS o
INNER JOIN customers AS c
ON c.id = o.customer_id
WHERE o.status = 'completed'
ORDER BY o.order_date;INNER JOIN повертає лише ті рядки, для яких умова ON виконується в обох таблицях.
У цьому прикладі кожне замовлення повинно мати клієнта завдяки зовнішньому ключу customer_id, але тип JOIN все одно відображає бізнес-вимогу: показати лише пов’язані записи.
Потрібно знайти клієнтів, у яких немає жодного замовлення зі статусом completed.
На перший погляд можна приєднати всі замовлення, а потім відфільтрувати статус у WHERE. Але це змінить логіку LEFT JOIN.
SELECT
c.id,
c.full_name
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE o.status = 'completed'
AND o.id IS NULL;Умова o.status = 'completed' відкидає рядки, де o дорівнює NULL. Тому результат буде порожнім.
SELECT
c.id,
c.full_name,
c.city
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.status = 'completed'
WHERE o.id IS NULL
ORDER BY c.id;Фільтр статусу розташований в ON. Тому LEFT JOIN спочатку зберігає всіх клієнтів, а для клієнтів без завершених замовлень значення з orders залишаються NULL.
Очікувано буде знайдено Дмитра Бондара: у нього є замовлення, але воно має статус pending.
Потрібно отримати виручку кожної категорії лише за завершеними замовленнями.
Важлива умова: категорії без продажів також повинні потрапити до результату з нульовою виручкою.
SELECT
c.id AS category_id,
c.name AS category_name,
COALESCE(
SUM(oi.quantity * oi.unit_price),
0
) AS revenue
FROM categories AS c
LEFT JOIN products AS p
ON p.category_id = c.id
LEFT JOIN order_items AS oi
ON oi.product_id = p.id
LEFT JOIN orders AS o
ON o.id = oi.order_id
AND o.status = 'completed'
GROUP BY
c.id,
c.name
ORDER BY c.id;Тут використано ланцюжок об’єднань:
categories → products → order_items → ordersОднак є важлива проблема: якщо orders приєднано через LEFT JOIN, рядки позицій незавершених замовлень все ще залишаються в результаті. Для них поля o будуть NULL, але вираз oi.quantity * oi.unit_price не стане автоматично нульовим.
Тому коректніший варіант — додати перевірку статусу безпосередньо до суми:
SELECT
c.id AS category_id,
c.name AS category_name,
COALESCE(
SUM(
CASE
WHEN o.status = 'completed'
THEN oi.quantity * oi.unit_price
ELSE 0
END
),
0
) AS revenue
FROM categories AS c
LEFT JOIN products AS p
ON p.category_id = c.id
LEFT JOIN order_items AS oi
ON oi.product_id = p.id
LEFT JOIN orders AS o
ON o.id = oi.order_id
GROUP BY
c.id,
c.name
ORDER BY c.id;У PostgreSQL зручніше використати FILTER:
SELECT
c.id AS category_id,
c.name AS category_name,
COALESCE(
SUM(oi.quantity * oi.unit_price)
FILTER (WHERE o.status = 'completed'),
0
) AS revenue
FROM categories AS c
LEFT JOIN products AS p
ON p.category_id = c.id
LEFT JOIN order_items AS oi
ON oi.product_id = p.id
LEFT JOIN orders AS o
ON o.id = oi.order_id
GROUP BY
c.id,
c.name
ORDER BY c.id;FILTER дозволяє застосувати умову лише до конкретної агрегатної функції.
Потрібно знайти завершені замовлення, загальна сума яких перевищує 100.
Результат повинен містити:
ідентифікатор замовлення;
ім’я клієнта;
кількість позицій;
загальну суму.
SELECT
o.id AS order_id,
c.full_name,
COUNT(oi.product_id) AS item_count,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
INNER JOIN customers AS c
ON c.id = o.customer_id
INNER JOIN order_items AS oi
ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY
o.id,
c.full_name
HAVING SUM(oi.quantity * oi.unit_price) > 100
ORDER BY order_total DESC;WHERE фільтрує окремі рядки до групування, а HAVING — уже сформовані групи.
Наприклад:
WHERE o.status = 'completed' залишає лише позиції завершених замовлень;
HAVING SUM(...) > 100 залишає лише замовлення з потрібною сумою.
Потрібно знайти товари, які не входили до жодного завершеного замовлення.
Для задачі «знайти записи, для яких не існує пов’язаного запису» зручно використовувати NOT EXISTS.
NOT EXISTSSELECT
p.id,
p.name,
p.price
FROM products AS p
WHERE NOT EXISTS (
SELECT 1
FROM order_items AS oi
INNER JOIN orders AS o
ON o.id = oi.order_id
WHERE oi.product_id = p.id
AND o.status = 'completed'
)
ORDER BY p.id;Результат міститиме принтер. Також до нього потрапили б товари, які є лише в скасованих або незавершених замовленнях.
Ту саму задачу можна розв’язати за допомогою LEFT JOIN:
SELECT
p.id,
p.name,
p.price
FROM products AS p
LEFT JOIN order_items AS oi
ON oi.product_id = p.id
LEFT JOIN orders AS o
ON o.id = oi.order_id
AND o.status = 'completed'
WHERE o.id IS NULL
ORDER BY p.id;Для антиоб’єднань NOT EXISTS часто є зрозумілішим, особливо коли умова перевірки складається з кількох частин.
Потрібно показати для кожного клієнта:
ім’я;
дату останнього завершеного замовлення;
ідентифікатор цього замовлення.
Клієнти без завершених замовлень теж повинні бути у результаті.
ROW_NUMBERWITH ranked_orders AS (
SELECT
o.id AS order_id,
o.customer_id,
o.order_date,
ROW_NUMBER() OVER (
PARTITION BY o.customer_id
ORDER BY o.order_date DESC, o.id DESC
) AS row_number
FROM orders AS o
WHERE o.status = 'completed'
)
SELECT
c.id AS customer_id,
c.full_name,
ro.order_id,
ro.order_date
FROM customers AS c
LEFT JOIN ranked_orders AS ro
ON ro.customer_id = c.id
AND ro.row_number = 1
ORDER BY c.id;Спочатку у CTE кожне завершене замовлення отримує номер у межах свого клієнта:
1 — найновіше;
2 — наступне;
і так далі.
Потім до клієнтів приєднується лише рядок із номером 1.
Умова ro.row_number = 1 розташована в ON, а не в WHERE. Це дозволяє зберегти клієнтів, для яких відповідного замовлення немає.
Потрібно:
порахувати загальні витрати кожного клієнта за завершеними замовленнями;
знайти середні витрати для кожного міста;
показати клієнтів, чиї витрати вищі за середні у їхньому місті.
Клієнти без завершених замовлень мають вважатися такими, що витратили 0.
WITH customer_spending AS (
SELECT
c.id AS customer_id,
c.full_name,
c.city,
COALESCE(
SUM(oi.quantity * oi.unit_price)
FILTER (WHERE o.status = 'completed'),
0
) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
LEFT JOIN order_items AS oi
ON oi.order_id = o.id
GROUP BY
c.id,
c.full_name,
c.city
),
city_averages AS (
SELECT
city,
AVG(total_spent) AS average_spent
FROM customer_spending
GROUP BY city
)
SELECT
cs.customer_id,
cs.full_name,
cs.city,
cs.total_spent,
ca.average_spent
FROM customer_spending AS cs
INNER JOIN city_averages AS ca
ON ca.city = cs.city
WHERE cs.total_spent > ca.average_spent
ORDER BY cs.total_spent DESC;У першому CTE формується один рядок на клієнта. У другому — один рядок на місто. Потім ці результати об’єднуються за полем city.
Для Києва:
Анна витратила більше;
Богдан витратив менше.
Тому Анна потрапить до результату. Олена єдина у своїх даних в Одесі, тому її витрати дорівнюють середньому, а не перевищують його.
Потрібно знайти завершені замовлення, у яких є товари щонайменше з двох різних категорій.
SELECT
o.id AS order_id,
c.full_name,
COUNT(DISTINCT p.category_id) AS category_count,
SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
INNER JOIN customers AS c
ON c.id = o.customer_id
INNER JOIN order_items AS oi
ON oi.order_id = o.id
INNER JOIN products AS p
ON p.id = oi.product_id
WHERE o.status = 'completed'
GROUP BY
o.id,
c.full_name
HAVING COUNT(DISTINCT p.category_id) >= 2
ORDER BY o.id;Умова COUNT(DISTINCT p.category_id) потрібна тому, що в одному замовленні може бути кілька товарів з тієї самої категорії.
Наприклад, замовлення з двома кріслами та лампою містить два різні товари, але лише одну категорію. Воно не повинно потрапити до результату.
Потрібно визначити пари товарів, які найчастіше купувалися разом в одному завершеному замовленні.
Для цього таблицю order_items потрібно приєднати саму до себе. Це називається self join.
SELECT
p1.name AS product_1,
p2.name AS product_2,
COUNT(DISTINCT oi1.order_id) AS orders_together
FROM order_items AS oi1
INNER JOIN order_items AS oi2
ON oi2.order_id = oi1.order_id
AND oi1.product_id < oi2.product_id
INNER JOIN orders AS o
ON o.id = oi1.order_id
AND o.status = 'completed'
INNER JOIN products AS p1
ON p1.id = oi1.product_id
INNER JOIN products AS p2
ON p2.id = oi2.product_id
GROUP BY
p1.name,
p2.name
ORDER BY orders_together DESC, product_1, product_2;Умова:
oi1.product_id < oi2.product_idпотрібна з двох причин:
товар не повинен утворювати пару сам із собою;
пара A + B не повинна дублюватися як B + A.
Якби цієї умови не було, кожна пара з’явилася б двічі.
INNER JOINВикористовуйте, коли потрібні лише записи, для яких відповідність існує в обох таблицях.
Приклад:
FROM orders AS o
INNER JOIN customers AS c
ON c.id = o.customer_idLEFT JOINВикористовуйте, коли потрібно зберегти всі рядки лівої таблиці, навіть якщо відповідності справа немає.
Приклад:
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.idЦе підходить для задач:
клієнти без замовлень;
категорії без товарів;
товари без продажів;
звіти з нульовими значеннями.
FULL OUTER JOINВикористовуйте, коли потрібно зберегти всі рядки з обох таблиць, включно з непов’язаними.
У наведеній моделі даних повний зовнішній join не потрібен для основних звітів, але його логіка важлива: на відміну від LEFT JOIN, він не відкидає непов’язані рядки правої таблиці.
WHERE після LEFT JOINТакий запит фактично поводиться як INNER JOIN:
SELECT c.full_name, o.id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
WHERE o.status = 'completed';Якщо потрібно зберегти клієнтів без завершених замовлень, перенесіть умову до ON:
SELECT c.full_name, o.id
FROM customers AS c
LEFT JOIN orders AS o
ON o.customer_id = c.id
AND o.status = 'completed';COUNT(*) для підрахунку пов’язаних записівПісля LEFT JOIN COUNT(*) може повернути 1, навіть якщо пов’язаного запису немає, адже сам рядок лівої таблиці все одно існує.
Замість цього рахуйте конкретний стовпець правої таблиці:
COUNT(o.id)DISTINCTЯкщо об’єднати клієнтів із замовленнями та позиціями, один клієнт може з’явитися багато разів. Це нормально для деталізованого результату, але неправильно для підрахунку клієнтів.
Для кількості унікальних клієнтів використовуйте:
COUNT(DISTINCT c.id)Якщо до клієнта одночасно приєднати замовлення та іншу таблицю з багатьма рядками на одне замовлення, кількість рядків може перемножитися.
Наприклад, одне замовлення з трьома позиціями не повинно стати трьома замовленнями. У таких випадках:
агрегуйте на потрібному рівні;
використовуйте COUNT(DISTINCT ...);
перевіряйте кардинальність кожного JOIN.
ONУмова об’єднання повинна використовувати всі необхідні ключі. Якщо приєднати таблиці лише за неочевидним полем або пропустити частину складеного ключа, з’являться неправильні комбінації рядків.
Для таблиці order_items у цій моделі правильний ідентифікатор рядка — пара:
(order_id, product_id)DISTINCT для приховування помилкиDISTINCT видаляє дублікати з результату, але не виправляє неправильну логіку об’єднання. Якщо рядки стали дублюватися через помилковий JOIN, спочатку потрібно виправити умову ON.
INNER JOIN повертає лише пов’язані рядки.
LEFT JOIN зберігає всі рядки лівої таблиці.
Умова у WHERE може перетворити LEFT JOIN на фактичний INNER JOIN.
Для пошуку відсутніх відповідностей використовуйте LEFT JOIN ... IS NULL або NOT EXISTS.
Для агрегатів після кількох JOIN перевіряйте, чи не множаться рядки.
COUNT(DISTINCT ...) допомагає рахувати унікальні сутності.
HAVING фільтрує групи після агрегації.
Self join дозволяє порівнювати рядки однієї таблиці між собою.
Для складних звітів зручно спочатку підготувати проміжні результати в CTE, а потім об’єднати їх через JOIN.