Урок 6. Объединение таблиц — JOIN

В учебном магазине пока одна таблица — товары. Теперь появятся покупатели и заказы. В заказе не повторяют имя покупателя и название товара, а хранят только их номера: customer_id и product_id. Чтобы увидеть заказ целиком, таблицы нужно соединить — это делает JOIN.

Новые таблицы

Создайте их и заполните:

CREATE TABLE customers (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT
);

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,   -- номер покупателя из customers
    product_id  INTEGER NOT NULL,   -- номер товара из products
    qty         INTEGER NOT NULL,
    order_date  TEXT    NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Анна', 'Минск'),
    ('Борис', 'Гомель'),
    ('Вера', 'Минск'),
    ('Глеб', 'Брест'),
    ('Дарья', 'Гродно');

INSERT INTO orders (customer_id, product_id, qty, order_date) VALUES
    (1, 1, 10, '2026-09-01'),
    (1, 3, 1, '2026-09-01'),
    (2, 6, 1, '2026-09-03'),
    (3, 2, 20, '2026-09-05'),
    (3, 1, 5, '2026-09-10'),
    (4, 7, 1, '2026-09-12'),
    (2, 4, 2, '2026-09-15');

Сама по себе таблица заказов мало что говорит:

JOIN … ON

SELECT orders.id, customers.name, orders.qty
FROM orders
JOIN customers ON customers.id = orders.customer_id
ORDER BY orders.id;

JOIN берёт каждую строку orders и ищет к ней строки customers, для которых верно условие после ON: номер покупателя совпадает. Найденные пары склеиваются в одну строку ответа.

Столбцы записаны с именем таблицы через точку: orders.id, customers.name. Это обязательно, когда столбец с таким именем есть в обеих таблицах: id есть и там, и там. Без уточнения SQLite ответит ошибкой ambiguous column name: id («неоднозначное имя столбца»).

Псевдонимы и три таблицы

Длинные имена таблиц сокращают псевдонимами: orders AS o или просто orders o. Присоединяется и третья таблица, товары, — JOIN повторяют сколько угодно раз:

SELECT o.id, c.name AS customer, p.name AS product, o.qty
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN products AS p ON p.id = o.product_id
ORDER BY o.id;

После соединения работает всё, что уже знакомо: WHERE, выражения, сортировка. Заказы из Минска с суммой:

SELECT o.id, c.name AS customer, p.name AS product, p.price * o.qty AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = o.product_id
WHERE c.city = 'Минск'
ORDER BY o.id;

JOIN и GROUP BY

Сколько потратил каждый покупатель:

SELECT c.name, SUM(p.price * o.qty) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN products p ON p.id = o.product_id
GROUP BY c.id, c.name
ORDER BY spent DESC;

Группировка идёт по c.id, а не только по имени: два разных покупателя могут оказаться тёзками. c.name тоже указан в GROUP BY, чтобы его можно было вывести — по правилу из прошлого урока.

LEFT JOIN — все строки левой таблицы

Дарьи в ответе нет: у неё нет заказов, и JOIN не нашёл ей пары. Обычный JOIN (полное имя — INNER JOIN) выводит только строки, у которых пара нашлась. LEFT JOIN выводит все строки левой таблицы (той, что до слова JOIN), а там, где пары нет, ставит NULL:

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
ORDER BY c.id, o.id;

Отсюда частый приём — найти строки без пары. Покупатели без заказов:

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Так же находятся товары, которые никто не заказывал: products p LEFT JOIN orders o ON o.product_id = p.id и WHERE o.id IS NULL — это глобус и сумка для ноутбука. Подробнее о разнице — справка Чем LEFT JOIN отличается от INNER JOIN.

Подсчёт с LEFT JOIN

Число заказов у каждого покупателя, включая тех, у кого их нет:

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

Здесь важно COUNT(o.id), а не COUNT(*). У Дарьи в соединении одна строка (с NULL вместо заказа), и COUNT(*) насчитал бы ей 1 заказ. COUNT(o.id) пропускает NULL и даёт честный 0.

Другие виды JOIN

• RIGHT JOIN — зеркало LEFT JOIN: все строки правой таблицы. Обычно вместо него меняют таблицы местами и пишут LEFT JOIN.
• FULL OUTER JOIN — все строки обеих таблиц, пары — где нашлись. Есть в PostgreSQL и в SQLite 3.39 и новее, в MySQL его нет.
• CROSS JOIN — каждая строка с каждой, без условия.

Встречается и старая запись: таблицы через запятую, а условие — в WHERE: FROM orders o, customers c WHERE c.id = o.customer_id. Она работает так же, как JOIN, но условие соединения легко потерять среди других условий. Пишите JOIN … ON.

Коротко

• JOIN таблица ON условие соединяет строки двух таблиц по совпадению ключей.
• Одноимённые столбцы уточняют таблицей: o.id, c.name; таблицам дают короткие псевдонимы.
• JOIN (INNER JOIN) — только строки с парой; LEFT JOIN — все строки левой таблицы, без пары — NULL.
• Строки без пары: LEFT JOIN … WHERE правая.id IS NULL.
• С LEFT JOIN считайте COUNT(правая.id), а не COUNT(*).

Проверьте себя