Пошук уроків, статей та іншого контенту
Навчитеся об’єднувати таблиці та повертати лише записи, що мають відповідні збіги в обох таблицях.
INNER JOININNER JOIN об’єднує рядки з двох таблиць за спільною умовою. У результат потрапляють лише ті записи, для яких знайдено відповідність в обох таблицях.
Наприклад, є таблиці:
customers — клієнти;
orders — замовлення клієнтів.
Щоб отримати замовлення разом з іменами клієнтів, потрібно об’єднати ці таблиці через спільний ідентифікатор.
SELECT таблиця_1.колонка,
таблиця_2.колонка
FROM таблиця_1
INNER JOIN таблиця_2
ON таблиця_1.спільна_колонка = таблиця_2.спільна_колонка;Ключове слово ON містить умову об’єднання таблиць.
У більшості випадків таблиці пов’язуються через:
первинний ключ однієї таблиці;
зовнішній ключ іншої таблиці.
Створимо дві таблиці та додамо тестові дані:
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
product TEXT NOT NULL
);
INSERT INTO customers (name)
VALUES
('Олена'),
('Андрій'),
('Марія');
INSERT INTO orders (customer_id, product)
VALUES
(1, 'Ноутбук'),
(1, 'Мишка'),
(2, 'Клавіатура'),
(99, 'Монітор');У таблиці customers є три клієнти. У таблиці orders є чотири замовлення.
Останнє замовлення має customer_id = 99. У наведеному прикладі такий запис не пройде через обмеження зовнішнього ключа, тому його потрібно або видалити, або створити таблиці без цього обмеження для демонстрації відсутньої відповідності.
Для коректного виконання прикладу використаємо тільки замовлення з наявними клієнтами:
TRUNCATE TABLE orders, customers RESTART IDENTITY CASCADE;
INSERT INTO customers (name)
VALUES
('Олена'),
('Андрій'),
('Марія');
INSERT INTO orders (customer_id, product)
VALUES
(1, 'Ноутбук'),
(1, 'Мишка'),
(2, 'Клавіатура');INNER JOINSELECT
customers.name,
orders.product
FROM customers
INNER JOIN orders
ON customers.id = orders.customer_id;Результат:
name | product
--------+------------
Олена | Ноутбук
Олена | Мишка
Андрій | КлавіатураКлієнтка Марія не потрапила до результату, оскільки в неї немає замовлень.
Це головна особливість INNER JOIN: він повертає тільки рядки, для яких умова в ON виконується в обох таблицях.
Ключове слово INNER можна не вказувати. Запит нижче працює так само:
SELECT
customers.name,
orders.product
FROM customers
JOIN orders
ON customers.id = orders.customer_id;JOIN без уточнення типу за замовчуванням означає INNER JOIN.
Якщо назви таблиць довгі, зручно використовувати псевдоніми:
SELECT
c.name,
o.product
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id;Ключове слово AS створює псевдонім:
c — псевдонім таблиці customers;
o — псевдонім таблиці orders.
У SQL для таблиць також часто використовують короткий запис без `AS:
SELECT
c.name,
o.product
FROM customers c
JOIN orders o
ON c.id = o.customer_id;Псевдоніми роблять запити коротшими та зручнішими для читання.
До результату JOIN можна застосувати WHERE.
Наприклад, отримаємо лише замовлення клієнтки Олени:
SELECT
c.name,
o.product
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id
WHERE c.name = 'Олена';Результат:
name | product
-------+---------
Олена | Ноутбук
Олена | МишкаСпочатку PostgreSQL знаходить відповідні рядки в обох таблицях, а потім залишає рядки, що відповідають умові WHERE.
Умова ON може містити кілька частин, об’єднаних оператором AND.
Наприклад:
SELECT
c.name,
o.product
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id
AND o.product <> 'Мишка';У результаті будуть замовлення клієнтів, крім замовлення на мишку.
Важливо відрізняти умову зв’язку таблиць від додаткової фільтрації:
ON c.id = o.customer_id
AND o.product <> 'Мишка'c.id = o.customer_id пов’язує таблиці;
o.product <> 'Мишка' додатково обмежує збіги.
INNER JOIN можна використовувати кілька разів в одному запиті.
Наприклад, додамо таблицю категорій товарів:
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
ALTER TABLE orders
ADD COLUMN category_id INTEGER REFERENCES categories(id);
INSERT INTO categories (name)
VALUES
('Електроніка'),
('Аксесуари');
UPDATE orders
SET category_id = CASE product
WHEN 'Ноутбук' THEN 1
WHEN 'Мишка' THEN 2
WHEN 'Клавіатура' THEN 2
END;Тепер можна отримати ім’я клієнта, товар і категорію:
SELECT
c.name AS customer_name,
o.product,
cat.name AS category_name
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id
INNER JOIN categories AS cat
ON o.category_id = cat.id;Кожен INNER JOIN додає ще одну умову відповідності. Якщо для рядка не буде відповідності хоча б в одній таблиці, цей рядок не потрапить до результату.
INNER JOIN і відсутні значенняРозглянемо клієнтів:
Олена
Андрій
МаріяЗамовлення є лише в Олени та Андрія. Запит:
SELECT
c.name,
o.product
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id;не покаже Марію, тому що для неї немає відповідного рядка в orders.
INNER JOIN не додає рядки з порожніми значеннями замість відсутніх даних. Він просто виключає записи без відповідності.
Якщо в обох таблицях є колонка з однаковою назвою, потрібно явно вказати таблицю:
SELECT
c.id,
c.name,
o.id,
o.product
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id;Щоб зробити назви колонок результату зрозумілішими, використовуйте псевдоніми колонок:
SELECT
c.id AS customer_id,
c.name AS customer_name,
o.id AS order_id,
o.product
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id;Тут:
customer_id — ідентифікатор клієнта;
order_id — ідентифікатор замовлення;
customer_name — ім’я клієнта.
У запиті SQL частини записані в такому порядку:
SELECT ...
FROM ...
INNER JOIN ...
ON ...
WHERE ...Для розуміння результату корисно мислити так:
PostgreSQL визначає таблицю з FROM.
Об’єднує її з іншою таблицею за умовою ON.
Залишає рядки, які відповідають WHERE.
Повертає колонки, указані в SELECT.
Наприклад:
SELECT
c.name,
o.product
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id
WHERE o.product = 'Ноутбук';Цей запит спочатку знаходить клієнтів та їхні замовлення, а потім залишає лише замовлення на ноутбук.
ONНеправильно:
SELECT
c.name,
o.product
FROM customers AS c
INNER JOIN orders AS o;Для INNER JOIN потрібно вказати умову об’єднання:
SELECT
c.name,
o.product
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id;Потрібно порівнювати колонки, які справді описують зв’язок між таблицями:
ON c.id = o.customer_idПорівняння, наприклад, імені клієнта з назвою товару не створить коректного зв’язку.
Якщо обидві таблиці мають колонку id, такий запис може спричинити помилку:
SELECT id
FROM customers AS c
JOIN orders AS o
ON c.id = o.customer_id;Краще вказати, з якої таблиці потрібно взяти колонку:
SELECT c.id AS customer_id
FROM customers AS c
JOIN orders AS o
ON c.id = o.customer_id;INNER JOIN не показує записи без відповідності. Якщо потрібно побачити всіх клієнтів, зокрема тих, хто не має замовлень, потрібен інший тип об’єднання — LEFT JOIN. Він розглядається окремо.
Отримаємо список замовлень із даними клієнта та відсортуємо його за іменем клієнта:
SELECT
c.name AS customer_name,
o.product
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id
ORDER BY c.name, o.product;Повний приклад можна виконати після створення таблиць:
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
product TEXT NOT NULL
);
INSERT INTO customers (name)
VALUES
('Олена'),
('Андрій'),
('Марія');
INSERT INTO orders (customer_id, product)
VALUES
(1, 'Ноутбук'),
(1, 'Мишка'),
(2, 'Клавіатура');
SELECT
c.name AS customer_name,
o.product
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id
ORDER BY c.name, o.product;INNER JOIN об’єднує дані з двох або більше таблиць.
У результат потрапляють лише рядки з відповідностями в усіх об’єднаних таблицях.
Умова зв’язку записується після ON.
JOIN без указаного типу означає INNER JOIN.
Псевдоніми таблиць скорочують запити та роблять їх зрозумілішими.
Для колонок з однаковими назвами потрібно вказувати ім’я таблиці або її псевдонім.
Записи без відповідної пари в іншій таблиці INNER JOIN не повертає.