У вас три таблицы: пользователи, заказы, товары. Вам нужно показать имена покупателей рядом с их заказами и суммой покупки. Данные есть — но они разбросаны по разным таблицам. Вот для этого и нужен JOIN.
В этом уроке вы научитесь соединять таблицы, считать агрегатную статистику и писать подзапросы. После этого сможете работать с настоящей реляционной базой — не учебной, а той что встречается в реальных проектах.
Весь урок мы работаем с тремя связанными таблицами. Создайте их в dbfiddle.uk — вставьте скрипт в поле Schema SQL и нажмите Run.
CREATE TABLE users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL ); CREATE TABLE products ( id INTEGER PRIMARY KEY, title TEXT NOT NULL, price REAL NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, user_id INTEGER REFERENCES users(id), product_id INTEGER REFERENCES products(id), quantity INTEGER NOT NULL, created_at TEXT NOT NULL ); INSERT INTO users VALUES (1, 'Алия', 'aliya@example.com'), (2, 'Максим', 'max@example.com'), (3, 'Наташа', 'nat@example.com'); INSERT INTO products VALUES (1, 'Ноутбук', 75000), (2, 'Мышь', 1200), (3, 'Монитор', 25000); INSERT INTO orders VALUES (1, 1, 1, 1, '2024-01-10'), (2, 1, 2, 2, '2024-01-15'), (3, 2, 3, 1, '2024-02-01'), (4, 2, 2, 1, '2024-02-05');
Обратите внимание: у Наташи нет ни одного заказа. Это сделано специально — когда дойдём до LEFT JOIN, поймёте зачем.
Поля user_id и product_id в таблице orders — это внешние ключи. Они содержат не само имя пользователя и не название товара, а только их id из соответствующих таблиц. Именно по этим ключам мы будем склеивать таблицы вместе.
Допустим, нужно посмотреть все заказы с именами покупателей. Имя лежит в users, заказы — в orders. Нужно соединить таблицы:
SELECT o.id, u.name, o.quantity, o.created_at FROM orders o INNER JOIN users u ON o.user_id = u.id;
Результат:
id | name | quantity | created_at ---|--------|----------|----------- 1 | Алия | 1 | 2024-01-10 2 | Алия | 2 | 2024-01-15 3 | Максим | 1 | 2024-02-01 4 | Максим | 1 | 2024-02-05
Четыре строки — по одной на каждый заказ. Наташи нет: у неё нет заказов, и INNER JOIN просто не включил её в результат. В этом и суть: INNER JOIN возвращает только строки, у которых есть совпадение в обеих таблицах. Нет пары — строка выпадает.
Условие ON o.user_id = u.id — это и есть условие соединения. Именно оно говорит базе: «связывай строки, где user_id в orders совпадает с id в users».
Несколько слов об алиасах: FROM orders o означает, что дальше в запросе o — это сокращение для orders, а u — для users. Без алиасов пришлось бы везде писать полное имя таблицы. На коротких запросах разница небольшая, но когда таблиц три-четыре с длинными названиями — алиасы превращают запрос из нечитаемого в понятный.
Диаграмма Венна для INNER JOIN:
┌──┐ ┌──┐ │ │██│ │ └──┘ └──┘ Только пересечение
Теперь тот же запрос, но через LEFT JOIN — и поменяем левую таблицу на users:
SELECT u.name, o.id, o.created_at FROM users u LEFT JOIN orders o ON u.id = o.user_id;
Результат:
name | id | created_at --------|------|------------ Алия | 1 | 2024-01-10 Алия | 2 | 2024-01-15 Максим | 3 | 2024-02-01 Максим | 4 | 2024-02-05 Наташа | NULL | NULL
Пять строк вместо четырёх. Наташа появилась — но в колонках из orders у неё NULL, потому что заказов нет.
LEFT JOIN возвращает все строки из левой таблицы и совпадения из правой. Нет пары в правой — ставится NULL.
Диаграмма Венна для LEFT JOIN:
┌──┐ ┌──┐ │██│██│ │ └──┘ └──┘ Вся левая + пересечение
Когда что применять: INNER JOIN — когда вам нужны только строки с совпадениями в обеих таблицах. LEFT JOIN — когда важно сохранить все строки из левой таблицы, даже если в правой ничего не нашлось. Классический пример: отчёт «все пользователи и их заказы». Если использовать INNER JOIN, пользователи без заказов исчезнут из результата. LEFT JOIN сохраняет всех.
Если NULL в результате нужно заменить на что-то осмысленное — используйте COALESCE. Функция принимает список значений и возвращает первое не-NULL:
SELECT u.name, COALESCE(CAST(o.id AS TEXT), 'нет заказов') AS order_info FROM users u LEFT JOIN orders o ON u.id = o.user_id;
У Наташи теперь отобразится «нет заказов» вместо NULL.
Соединять таблицы умеем. Теперь — считать статистику по ним.
Агрегатные функции сворачивают набор строк в одно значение: общее количество, сумма, среднее, минимум, максимум.
SELECT COUNT(*) AS total_orders, SUM(p.price * o.quantity) AS total_revenue, AVG(p.price) AS avg_price, MIN(p.price) AS min_price, MAX(p.price) AS max_price FROM orders o JOIN products p ON o.product_id = p.id;
Важная деталь про COUNT: COUNT(*) считает все строки, включая те где есть NULL. COUNT(колонка) считает только строки, где значение этой колонки не NULL. Разница проявляется, когда таблицы соединены через LEFT JOIN:
SELECT COUNT(*) AS all_rows, COUNT(o.id) AS orders_only FROM users u LEFT JOIN orders o ON u.id = o.user_id;
all_rows | orders_only ---------|------------ 5 | 4
COUNT(*) вернул 5 — все строки результата, включая Наташу с NULL. COUNT(o.id) вернул 4 — только строки где o.id не NULL. Используйте COUNT(колонка) когда нужно посчитать именно непустые значения.
Агрегатные функции без GROUP BY дают одно число по всей выборке. GROUP BY разбивает выборку на группы и применяет агрегат к каждой группе отдельно.
Посчитаем количество заказов по каждому пользователю:
SELECT user_id, COUNT(*) AS order_count FROM orders GROUP BY user_id;
user_id | order_count ---------|------------ 1 | 2 2 | 2
Добавим JOIN чтобы видеть имена, и посчитаем сумму покупок:
SELECT u.name, COUNT(o.id) AS order_count, SUM(p.price * o.quantity) AS total FROM users u LEFT JOIN orders o ON u.id = o.user_id LEFT JOIN products p ON o.product_id = p.id GROUP BY u.id, u.name;
name | order_count | total --------|-------------|-------- Алия | 2 | 77400 Максим | 2 | 26200 Наташа | 0 | NULL
Правило GROUP BY: каждая колонка в SELECT должна либо стоять в GROUP BY, либо быть обёрнута в агрегатную функцию. Нарушение этого правила — ошибка в PostgreSQL и MySQL. SQLite технически разрешает нарушение, но вернёт значение из случайной строки группы. Результат непредсказуем. Соблюдайте правило всегда, независимо от СУБД.
Теперь — фильтрация по результатам группировки. Нам нужны только пользователи с более чем одним заказом:
SELECT u.name, COUNT(o.id) AS order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.id, u.name HAVING COUNT(o.id) > 1;
HAVING — это фильтрация после группировки. WHERE фильтрует строки до группировки, HAVING — результаты групп после. Написать WHERE COUNT(o.id) > 1 не получится: в момент выполнения WHERE агрегаты ещё не посчитаны. Простая мнемоника: WHERE — для строк, HAVING — для групп.
Ещё один инструмент — DISTINCT. Убирает дубликаты из результата:
SELECT DISTINCT user_id FROM orders;
Без DISTINCT вернётся четыре строки — по одной на каждый заказ. С DISTINCT — только уникальные значения user_id: 1 и 2. Пригодится когда нужно быстро проверить, какие значения вообще встречаются в данных.
Подзапрос — это SELECT внутри другого SELECT. Результат внутреннего запроса используется как условие или источник данных для внешнего.
Скалярный подзапрос в WHERE. Найдём заказы с товарами, цена которых выше средней:
SELECT o.id, p.title, p.price FROM orders o JOIN products p ON o.product_id = p.id WHERE p.price > (SELECT AVG(price) FROM products);
Сначала выполняется внутренний запрос — SELECT AVG(price) FROM products — и возвращает одно число. Потом это число используется в условии WHERE внешнего запроса. База как будто подставляет результат подзапроса на место скобок.
Подзапрос с IN. Найдём пользователей, которые покупали товары дороже 10 000:
SELECT DISTINCT u.name FROM users u WHERE u.id IN ( SELECT o.user_id FROM orders o JOIN products p ON o.product_id = p.id WHERE p.price > 10000 );
Внутренний запрос возвращает список user_id, внешний проверяет вхождение в этот список через IN. Читается почти как обычная фраза: «выбери пользователей, чей id есть в списке тех, кто заказывал дорогие товары».
EXISTS и коррелированный подзапрос. EXISTS проверяет не что именно вернул подзапрос, а сам факт — вернул ли он хоть одну строку:
SELECT u.name FROM users u WHERE EXISTS ( SELECT 1 FROM orders o WHERE o.user_id = u.id );
SELECT 1 — стандартный приём: нам не важно содержимое результата, только его наличие. Как только найдётся первая подходящая строка, EXISTS вернёт true и остановится, не перебирая остальные.
Заметьте: внутренний запрос ссылается на u.id из внешнего. Это называется коррелированный подзапрос — он выполняется отдельно для каждой строки внешнего запроса. Для каждого пользователя база заново запускает внутренний SELECT с конкретным user_id. Именно поэтому на больших таблицах коррелированный подзапрос работает медленнее, чем JOIN.
| Задача | Инструмент |
|---|---|
| Нужны данные из нескольких таблиц в одном результате | JOIN |
| Нужно объединить строки по условию | JOIN |
| Нужно одно агрегированное значение как условие | Подзапрос |
| Нужно проверить существование строк | EXISTS |
| Логика читается понятнее через вложение | Подзапрос |
Если оба варианта решают задачу — выбирайте тот, который легче читать. На больших данных JOIN обычно быстрее коррелированного подзапроса.
После импорта или миграции полезно убедиться, что в orders нет заказов, которые ссылаются на несуществующих пользователей. Это называется анти-JOIN: ищем строки из одной таблицы, у которых нет пары в другой.
SELECT o.id, o.user_id FROM orders o LEFT JOIN users u ON o.user_id = u.id WHERE u.id IS NULL;
Пустой результат — отлично, висячих заказов нет. Если что-то нашлось — вы видите точные id проблемных строк и можете разобраться что пошло не так.
Типичная задача для API дашборда или аналитики:
SELECT p.title, SUM(p.price * o.quantity) AS revenue FROM orders o JOIN products p ON o.product_id = p.id GROUP BY p.id, p.title ORDER BY revenue DESC LIMIT 3;
JOIN достаёт цены из products, SUM считает выручку по каждому товару, GROUP BY группирует по товару, ORDER BY сортирует по убыванию, LIMIT обрезает до трёх строк.
В Room аннотация @Relation позволяет описать связь между двумя Entity-классами, и Room сам генерирует JOIN-запрос под капотом. Вы пишете Kotlin, а SQL формируется за вас. Но Room всё равно выполняет JOIN — и когда появятся проблемы с производительностью, нужно смотреть на сгенерированный SQL. Именно поэтому понимание JOIN здесь не «теория ради теории», а рабочий навык.
Агент работает с базой документов. Каждый документ может иметь несколько тегов — это связь «многие ко многим» через промежуточную таблицу:
SELECT d.content FROM documents d JOIN document_tags dt ON d.id = dt.document_id JOIN tags t ON dt.tag_id = t.id WHERE t.name = 'sql' AND d.updated_at > '2024-01-01' ORDER BY d.updated_at DESC LIMIT 5;
Два JOIN потому что documents и tags не связаны напрямую — только через document_tags. Такие запросы пишутся вручную когда нужен точный контроль над тем, что попадёт в контекст агента.
Проверка понимания материала
Доступно после входаПройдите квиз и сразу закрепите тему, когда откроете интерактивный режим урока.
JOIN — базовый инструмент работы с реляционными базами. INNER JOIN соединяет только строки с совпадениями в обеих таблицах. LEFT JOIN сохраняет все строки из левой — это нужно всегда когда важно не потерять записи без пары.
Агрегатные функции COUNT, SUM, AVG, MIN, MAX в сочетании с GROUP BY превращают сырые строки в статистику. HAVING фильтрует результаты после группировки — там где WHERE уже не работает. Правило: каждая колонка в SELECT либо агрегирована, либо стоит в GROUP BY.
Подзапросы и EXISTS решают задачи которые сложно выразить через JOIN: условие на агрегат, проверка существования, анти-паттерны. Если оба варианта работают — выбирайте тот, который понятнее читается, а на больших данных предпочитайте JOIN.
Чеклист:
В следующем уроке — SQL-4: надёжность. Индексы для скорости, транзакции для атомарности, защита от SQL-инъекций. Это то, что отличает учебный проект от продакшн-приложения.