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 ответит.

Вежливо.

Если мы правильно напишем запрос.