JOIN: читаємо дані з пов’язаних таблиць

Повертаємось до PostgreSQL.

У попередній лекції ти вивчив зв’язки між таблицями.

Ти дізнався:

• що таке первинний ключ;
• що таке зовнішній ключ;
• навіщо таблиці потрібно з’єднувати;
• як працює зв’язок один-до-багатьох;
• як PostgreSQL захищає ці зв’язки.

Дуже добре.

Тепер ми знаємо, як пов’язати таблиці.

Але є одна проблема.

Коли ми дивимось на продукти, ми бачимо щось таке:

Laptop | category_id = 1
Mouse  | category_id = 1
Chair  | category_id = 2

Корисно.

Але не дуже дружньо.

Люди не хочуть читати номери категорій.

Люди хочуть бачити назви.

Ми хочемо ось так:

Laptop | Electronics
Mouse  | Electronics
Chair  | Furniture

Саме тут нам допомагає JOIN.

JOIN дозволяє читати дані з кількох пов’язаних таблиць в одному запиті.

Ось тут реляційні бази даних починають виглядати справді потужно.

І трохи елегантно.

Наче SQL вдягнув піджак.

Що ти вивчиш

У цій лекції ти вивчиш:

• що робить JOIN;
• навіщо потрібен JOIN;
• як використовувати INNER JOIN;
• як з’єднувати таблиці через первинні та зовнішні ключі;
• як вибирати колонки з кількох таблиць;
• як використовувати псевдоніми таблиць;
• як уникати плутанини з однаковими назвами колонок;
• як використовувати LEFT JOIN;
• чим відрізняється INNER JOIN від LEFT JOIN;
• як з’єднувати більше ніж дві таблиці;
• типові помилки з JOIN.

До кінця цієї лекції ти зможеш об’єднувати дані з пов’язаних таблиць.

Це дуже важливий крок.

Без JOIN зв’язки між таблицями приховані.

З JOIN вони стають видимими.

База даних перестає показувати тобі таємничі числа.

І починає показувати нормальну корисну інформацію.

Дуже мило.

Дуже PostgreSQL.

Підготуй базу даних

Відкрий PostgreSQL:

sudo -iu postgres psql

Підключись до своєї бази даних:

\c learning_postgresql

Якщо такої бази ще немає, створи її:

CREATE DATABASE learning_postgresql;

Потім підключись:

\c learning_postgresql

Тепер видали старі таблиці, якщо вони існують:

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;

Ми видаляємо products першою, бо вона залежить від categories.

PostgreSQL поважає зв’язки.

Як строгий батько баз даних.

Створюємо таблиці

Створи таблицю categories:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

Створи таблицю products:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  category_id INTEGER REFERENCES categories(id)
);

Тепер у нас є дві пов’язані таблиці.

categories.id — це первинний ключ.

products.category_id — це зовнішній ключ.

Зв’язок виглядає так:

categories.id -> products.category_id

Одна категорія може мати багато продуктів.

Дуже нормально.

Дуже корисно.

Дуже база даних.

Додаємо дані

Додай категорії:

INSERT INTO categories (name)
VALUES
  ('Electronics'),
  ('Furniture'),
  ('Office');

Додай продукти:

INSERT INTO products (name, price, category_id)
VALUES
  ('Laptop', 900.00, 1),
  ('Mouse', 25.00, 1),
  ('Keyboard', 70.00, 1),
  ('Desk Chair', 150.00, 2),
  ('Bookshelf', 120.00, 2),
  ('Notebook', 5.00, 3),
  ('Pen', 2.00, 3);

Перевір обидві таблиці:

SELECT * FROM categories;
SELECT * FROM products;

Таблиця products показує category_id.

Це правильно.

Але не дуже красиво.

База даних знає зв’язок.

Тепер нам треба попросити її цей зв’язок показати.

Це робота для JOIN.

Проблема без JOIN

Виконай:

SELECT * FROM products;

Ти побачиш щось приблизно таке:

id | name       | price  | category_id
---|------------|--------|------------
1  | Laptop     | 900.00 | 1
2  | Mouse      | 25.00  | 1
3  | Keyboard   | 70.00  | 1
4  | Desk Chair | 150.00 | 2

Для PostgreSQL це корисно.

Але для людини category_id = 1 не дуже допомагає.

Нам потрібно бачити назву категорії.

Назва категорії лежить в іншій таблиці.

Тому нам треба об’єднати:

products
categories

Саме це і робить JOIN.

Що таке JOIN?

JOIN об’єднує рядки з двох або більше таблиць.

Він використовує зв’язок між таблицями.

Наприклад:

products.category_id = categories.id

Це означає:

Продукт належить до категорії, коли його category_id збігається з id категорії.

Базовий запит з JOIN виглядає так:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

Цей запит каже PostgreSQL:

Почни з products.
Приєднай categories.
З’єднай рядки там, де products.category_id дорівнює categories.id.
Покажи назву продукту і назву категорії.

Це серце JOIN.

Таблиці зустрічаються.

Рядки збігаються.

Дані стають читабельними.

Дуже цивілізовано.

Твій перший JOIN

Виконай цей запит:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

Ти побачиш щось таке:

name       | name
-----------|-------------
Laptop     | Electronics
Mouse      | Electronics
Keyboard   | Electronics
Desk Chair | Furniture
Bookshelf  | Furniture
Notebook   | Office
Pen        | Office

Працює.

Але є маленька проблема.

Обидві колонки називаються name.

Це може плутати.

Одна name — це назва продукту.

Інша name — це назва категорії.

PostgreSQL це дозволяє.

Твій мозок може протестувати.

Тому краще використати псевдоніми.

Псевдоніми колонок

Псевдонім колонки дає результату кращу назву.

Для цього використовують AS.

Приклад:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

Тепер результат зрозуміліший:

product_name | category_name
-------------|---------------
Laptop       | Electronics
Mouse        | Electronics
Keyboard     | Electronics
Desk Chair   | Furniture
Bookshelf    | Furniture
Notebook     | Office
Pen          | Office

Набагато краще.

Псевдоніми роблять результат легшим для читання.

База даних робить ту саму роботу.

Але людина менше страждає.

Це завжди добре.

Майже завжди.

Вибираємо більше колонок

Можна вибирати більше колонок з обох таблиць.

Приклад:

SELECT
  products.id,
  products.name AS product_name,
  products.price,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

Результат:

id | product_name | price  | category_name
---|--------------|--------|---------------
1  | Laptop       | 900.00 | Electronics
2  | Mouse        | 25.00  | Electronics
3  | Keyboard     | 70.00  | Electronics

Тепер у нас є корисні дані продукту і зрозумілі дані категорії.

Це дуже часто використовується в реальних застосунках.

Користувач не хоче бачити:

category_id = 1

Користувач хоче бачити:

Electronics

Твій застосунок може показувати гарніші дані, бо SQL зробив JOIN.

Хороший SQL робить frontend щасливішим.

А щасливий frontend менше скаржиться.

Іноді.

Псевдоніми таблиць

Писати повні назви таблиць може бути довго.

Ось так працює:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

Але можна коротше:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Тут:

• p означає products;
• c означає categories.

Це коротше.

І дуже поширено.

У професійному SQL псевдоніми таблиць використовуються постійно.

Не тому що розробники люблять загадкові букви.

Ну, можливо, трохи.

Але головна причина — запити стає легше писати.

JOIN з WHERE

WHERE можна використовувати разом з JOIN.

Приклад:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
WHERE c.name = 'Electronics';

Цей запит повертає тільки продукти з категорії Electronics.

Приклад результату:

Laptop   | 900.00 | Electronics
Mouse    | 25.00  | Electronics
Keyboard | 70.00  | Electronics

Це потужно.

Ми фільтруємо по даних з приєднаної таблиці.

Таблиця products не зберігає слово Electronics.

Вона зберігає category_id.

Але завдяки JOIN ми можемо фільтрувати по назві категорії.

Дуже корисно.

Дуже реально.

JOIN з ORDER BY

Також можна сортувати результати JOIN.

Приклад:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.price DESC;

Цей запит сортує продукти від найдорожчого до найдешевшого.

Можна також сортувати за назвою категорії:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY c.name ASC, p.name ASC;

Тут сортування йде спочатку за категорією.

Потім за назвою продукту.

Дуже акуратно.

Дуже організовано.

Наче база даних прибрала у своїй кімнаті.

Рідко, але красиво.

INNER JOIN

Коли ти пишеш:

JOIN categories

PostgreSQL сприймає це як:

INNER JOIN categories

Тобто ці два запити однакові:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

і:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.id;

INNER JOIN повертає тільки ті рядки, які мають збіг в обох таблицях.

Тобто якщо продукт не має відповідної категорії, він не з’явиться.

Але коли ми використовуємо зовнішній ключ, PostgreSQL зазвичай не дозволяє створити продукт з неіснуючою категорією.

Тому INNER JOIN часто саме те, що потрібно.

Чисто.

Строго.

Корисно.

LEFT JOIN

LEFT JOIN повертає всі рядки з лівої таблиці.

Навіть якщо у правій таблиці немає збігу.

Приклад:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

Це означає:

Покажи всі продукти.
Якщо категорія існує — покажи її.
Якщо ні — покажи NULL.

У нашій поточній таблиці кожен продукт має правильну категорію.

Тому результат буде такий самий, як з INNER JOIN.

Але LEFT JOIN дуже корисний, коли частина рядків може не мати пов’язаних даних.

Наприклад:

users and profiles
customers and orders
posts and comments

Іноді потрібно показати всіх користувачів, навіть якщо в когось немає профілю.

Саме тут потрібен LEFT JOIN.

INNER JOIN проти LEFT JOIN

Просте правило:

Використовуй INNER JOIN, коли тобі потрібні тільки рядки зі збігами.

Використовуй LEFT JOIN, коли хочеш бачити всі рядки з першої таблиці, навіть якщо у другій таблиці немає відповідного рядка.

Приклад:

INNER JOIN:
Тільки продукти з категоріями.

LEFT JOIN:
Усі продукти, а категорія — якщо вона є.

Уяви INNER JOIN як сувору вечірку:

Немає збігу — немає входу.

А LEFT JOIN як добрішу вечірку:

Заходь все одно. Якщо твого друга немає, покажемо NULL.

Вечірки баз даних дивні.

Але навчальні.

Створимо приклад для LEFT JOIN

Дозволимо продукт без категорії.

Спочатку видалимо стару таблицю products:

DROP TABLE IF EXISTS products;

Створимо її знову:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  category_id INTEGER REFERENCES categories(id)
);

Ця таблиця дозволяє category_id бути NULL, бо ми не написали NOT NULL.

Додай продукти:

INSERT INTO products (name, price, category_id)
VALUES
  ('Laptop', 900.00, 1),
  ('Mouse', 25.00, 1),
  ('Mystery Box', 50.00, NULL);

Тепер виконай INNER JOIN:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
INNER JOIN categories AS c
ON p.category_id = c.id;

Mystery Box не з’явиться.

Чому?

Бо в нього немає відповідної категорії.

Тепер виконай LEFT JOIN:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

Тепер Mystery Box з’явиться.

Його категорія буде NULL.

Ось різниця.

Маленьке слово.

Велика поведінка.

JOIN більше ніж двох таблиць

Можна з’єднувати більше ніж дві таблиці.

Уяви:

customers
orders
products

Але зараз зробимо простіший приклад:

customers
orders

Видали старі таблиці, якщо потрібно:

DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customers;

Створи таблицю customers:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Створи таблицю orders:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  total NUMERIC(10, 2) CHECK (total >= 0),
  customer_id INTEGER REFERENCES customers(id)
);

Додай клієнтів:

INSERT INTO customers (name)
VALUES
  ('Anna'),
  ('Marco'),
  ('Sofia');

Додай замовлення:

INSERT INTO orders (total, customer_id)
VALUES
  (49.99, 1),
  (120.00, 1),
  (35.50, 2);

Тепер зроби JOIN:

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

Результат:

order_id | customer_name | total
---------|---------------|-------
1        | Anna          | 49.99
2        | Anna          | 120.00
3        | Marco         | 35.50

Зверни увагу: Sofia не має замовлень.

З INNER JOIN вона не з’являється.

Але з LEFT JOIN ми можемо показати і її:

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

Тепер Sofia з’явиться з NULL у колонці total.

Це дуже корисно для звітів.

Наприклад:

Показати всіх клієнтів, навіть тих, хто ще нічого не замовив.

Це класична ситуація для LEFT JOIN.

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

Забути умову ON

Неправильно:

SELECT *
FROM products
JOIN categories;

Це не те, що тобі потрібно.

Ти маєш сказати PostgreSQL, як саме таблиці пов’язані.

Правильно:

SELECT *
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Умова ON — це міст.

Без неї ти не з’єднуєш таблиці правильно.

Ти просто запрошуєш хаос на вечерю.

З’єднувати неправильні колонки

Неправильно:

ON p.id = c.id

Це може випадково з’єднати рядки з однаковими номерами.

Але це не справжній зв’язок.

Правильно:

ON p.category_id = c.id

Зовнішній ключ вказує на первинний ключ.

Використовуй реальний зв’язок.

Не з’єднуй колонки тільки тому, що числа виглядають дружньо.

Числа брешуть.

Особливо в базах даних.

Не використовувати псевдоніми

Це працює:

SELECT products.name, categories.name
FROM products
JOIN categories
ON products.category_id = categories.id;

Але це зрозуміліше:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Псевдоніми роблять запити легшими для читання.

А читабельний SQL має менше шансів зрадити тебе опівночі.

Вибирати неоднозначні колонки

Це може бути проблемою:

SELECT name
FROM products
JOIN categories
ON products.category_id = categories.id;

Обидві таблиці мають колонку name.

PostgreSQL може поскаржитись або запит просто буде неясним.

Краще так:

SELECT
  products.name AS product_name,
  categories.name AS category_name
FROM products
JOIN categories
ON products.category_id = categories.id;

Коли колонки мають однакову назву, завжди будь конкретним.

База даних любить ясність.

Люди теж.

Здебільшого.

Практика

Напиши ці запити.

Покажи назви продуктів з назвами категорій:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Покажи назви продуктів, ціни і назви категорій:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id;

Покажи тільки продукти з категорії Electronics:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
WHERE c.name = 'Electronics';

Покажи продукти, відсортовані за ціною:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.price DESC;

Покажи всі продукти, навіть ті, що без категорії:

SELECT
  p.name AS product_name,
  c.name AS category_name
FROM products AS p
LEFT JOIN categories AS c
ON p.category_id = c.id;

Запускай запити.

Змінюй їх.

Ламай їх.

Виправляй їх.

Це все ще найкращий спосіб навчання.

SQL не заходить у голову тільки через теорію.

Він заходить через помилки.

Дуже навчальні помилки.

Міні-завдання

Створи дві таблиці:

authors
posts

Правила:

• один автор може мати багато постів;
• кожен пост належить одному автору;
• ім’я автора обов’язкове;
• назва поста обов’язкова;
• текст поста може бути довгим.

Створи таблиці:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  content TEXT,
  author_id INTEGER REFERENCES authors(id)
);

Додай дані:

INSERT INTO authors (name)
VALUES
  ('Anna'),
  ('Marco'),
  ('Sofia');

INSERT INTO posts (title, content, author_id)
VALUES
  ('First Post', 'Hello from PostgreSQL.', 1),
  ('SQL Notes', 'JOIN makes tables readable.', 1),
  ('Database Life', 'Data wants structure.', 2);

Тепер напиши запити, щоб:

• показати всі пости з іменами авторів;
• показати тільки пости автора Anna;
• показати авторів і їхні пости через LEFT JOIN;
• показати авторів навіть якщо в них немає постів;
• відсортувати пости за назвою.

Це завдання дуже схоже на реальні системи блогів.

Автори.

Пости.

Зв’язки.

Запити.

Дуже реально.

Без магії.

Тільки ключі і JOIN.

Красива маленька машина.

Підсумок

Сьогодні ти вивчив:

• JOIN об’єднує дані з пов’язаних таблиць;
• JOIN використовує зв’язки між таблицями;
• INNER JOIN повертає тільки рядки зі збігами;
• LEFT JOIN повертає всі рядки з лівої таблиці;
• зовнішні ключі допомагають правильно з’єднувати таблиці;
• ON визначає, як таблиці з’єднуються;
• псевдоніми таблиць роблять запити коротшими;
• псевдоніми колонок роблять результат зрозумілішим;
• WHERE може фільтрувати результат JOIN;
• ORDER BY може сортувати результат JOIN;
• з’єднання неправильних колонок дає неправильні дані;
• JOIN робить зв’язки видимими.

Це великий крок.

Тепер твої таблиці більше не ізольовані.

Ти можеш з’єднувати дані.

Ти можеш показувати читабельні результати.

Ти можеш перестати показувати користувачам загадкові ID і почати показувати нормальні назви.

Це прогрес бази даних.

Маленький запит.

Велике покращення.

Наступна лекція

У наступній лекції ми вивчимо агрегатні функції.

Ми будемо рахувати рядки.

Обчислювати суми.

Знаходити середні значення.

Знаходити мінімальні та максимальні значення.

Іншими словами, ми почнемо ставити PostgreSQL питання типу:

Скільки продуктів у нас є?
Яка загальна сума всіх замовлень?
Яка середня ціна?
Який продукт найдорожчий?

PostgreSQL відповість.

Чемно.

Якщо ми правильно напишемо запит.