INNER JOIN: пересечение таблиц

В предыдущем уроке вы узнали, зачем данные хранятся в разных таблицах и как первичные и внешние ключи создают связи. Теперь разберём первый и самый распространённый вид соединения — INNER JOIN. Он объединяет строки из двух таблиц там, где для каждой строки найдено совпадение в обеих таблицах одновременно.

Что делает INNER JOIN

INNER JOIN возвращает только те строки, для которых условие ON выполнилось в обеих таблицах. Если строка из первой таблицы не нашла пары во второй — она не попадёт в результат. И наоборот.

Синтаксис:

SELECT колонки
FROM   таблица_A
INNER JOIN таблица_B ON таблица_A.ключ = таблица_B.ключ;

Слово INNER можно опустить — просто JOIN по умолчанию означает INNER JOIN.

Базовый пример

Таблица customers:

id | name         | city
---+--------------+-------
1  | Анна Иванова | Москва
2  | Борис Петров | СПб
3  | Вера Сидорова| Казань

Таблица orders:

id | customer_id | product    | amount
---+-------------+------------+-------
1  | 1           | Ноутбук    | 89990
2  | 1           | Наушники   | 7800
3  | 2           | Мышь       | 1200

У Веры Сидоровой нет заказов. Запрос:

SELECT c.name, o.product, o.amount
FROM   customers c
JOIN   orders    o ON c.id = o.customer_id;

Результат:

name         | product  | amount
-------------+----------+-------
Анна Иванова | Ноутбук  | 89990
Анна Иванова | Наушники | 7800
Борис Петров | Мышь     | 1200

Вера Сидорова не появилась в результате — у неё нет совпадающих строк в orders. Именно это и делает INNER JOIN: возвращает только «пересечение» двух таблиц по условию соединения.

Проверь себя: если добавить четвёртый заказ с customer_id = 99 (которого нет в customers), попадёт ли он в результат INNER JOIN?

Алиасы таблиц и полные имена колонок

Когда соединяются несколько таблиц, одна и та же колонка может существовать в обеих. Например, обе таблицы имеют колонку id. Без уточнения СУБД не знает, id из какой таблицы вы имеете в виду.

Решение — всегда указывать имя таблицы или алиас:

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

Здесь c.id — id клиента, o.id — id заказа. Псевдоним AS order_id помогает различить их в результате.

Правило хорошего тона: всегда уточняйте принадлежность колонок в запросах с JOIN. Это предотвращает ошибку «column reference is ambiguous» и делает код понятным.

JOIN с WHERE

WHERE применяется после JOIN — сначала таблицы соединяются, потом фильтруются результирующие строки:

SELECT c.name, o.product, o.amount
FROM   customers c
JOIN   orders    o ON c.id = o.customer_id
WHERE  o.amount > 5000;

Результат — только заказы дороже 5000:

name         | product  | amount
-------------+----------+-------
Анна Иванова | Ноутбук  | 89990
Анна Иванова | Наушники | 7800

Порядок выполнения: FROM → JOIN → WHERE → SELECT → ORDER BY.

JOIN с GROUP BY и агрегатами

Соединение таблиц и агрегация отлично работают вместе. Типичная аналитическая задача: посчитать количество заказов и сумму по каждому клиенту:

SELECT c.name,
       COUNT(o.id)     AS orders_count,
       SUM(o.amount)   AS total_spent
FROM   customers c
JOIN   orders    o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC;

Результат:

name         | orders_count | total_spent
-------------+--------------+------------
Анна Иванова | 2            | 97790
Борис Петров | 1            | 1200

Снова Вера не появилась — у неё нет заказов, и INNER JOIN её исключил до группировки.

Несколько условий в ON

ON может содержать несколько условий через AND:

SELECT e.name, d.department_name
FROM   employees  e
JOIN   departments d ON e.department_id = d.id
                    AND d.location = 'Москва';

Второе условие в ON дополнительно ограничивает, какие строки считаются «совпадением». Это не то же самое, что добавить это условие в WHERE при INNER JOIN — результат одинаковый, но логически ON описывает правило соединения, WHERE — дополнительную фильтрацию.

Три таблицы через JOIN

Можно соединять несколько таблиц последовательно:

SELECT c.name, p.title, o.amount
FROM   orders    o
JOIN   customers c ON o.customer_id = c.id
JOIN   products  p ON o.product_id  = p.id
WHERE  o.amount > 1000;

Каждый JOIN добавляет новую таблицу к уже соединённому результату. Порядок JOIN в большинстве случаев не влияет на итог — оптимизатор PostgreSQL сам выбирает порядок выполнения. Детальнее о многотабличных запросах — в последнем уроке этого модуля.

INNER JOIN и NULL в ключах

Если значение внешнего ключа равно NULL, строка не попадёт в результат INNER JOIN. NULL не равен ничему, в том числе другому NULL. Это стандартное поведение.

orders: id=5, customer_id=NULL, product='Книга'

Такой заказ при JOIN customers ON o.customer_id = c.id не найдёт пары и будет исключён. Если нужно видеть и такие строки — нужен LEFT JOIN (следующий урок).

Практический пример: отчёт по продажам

Задача: для каждой категории товаров — количество заказов, суммарная выручка и средний чек, только для завершённых заказов.

SELECT p.category,
       COUNT(o.id)             AS orders_count,
       SUM(oi.quantity * oi.unit_price) AS revenue,
       AVG(oi.quantity * oi.unit_price) AS avg_order
FROM   order_items  oi
JOIN   orders       o  ON oi.order_id   = o.id
JOIN   products     p  ON oi.product_id = p.id
WHERE  o.status = 'completed'
GROUP BY p.category
ORDER BY revenue DESC;

Три таблицы, агрегаты, фильтрация — типичный аналитический запрос. INNER JOIN оставляет только те строки, где заказ существует, товар существует и у заказа есть статус.

Визуализация: как работает INNER JOIN

Представьте два множества строк и операцию пересечения. Слева — строки таблицы customers, справа — строки orders. INNER JOIN оставляет только те строки из левой таблицы, для которых нашлась хотя бы одна строка в правой, удовлетворяющая условию ON.

customers:          orders:          результат INNER JOIN:
[1] Анна     ←──→  [1] Ноутбук   →  [1, Анна, Ноутбук]
[1] Анна     ←──→  [2] Наушники  →  [1, Анна, Наушники]
[2] Борис    ←──→  [3] Мышь      →  [2, Борис, Мышь]
[3] Вера         (нет совпадения)    (исключена)

Вера не попала в результат. Гипотетический заказ с несуществующим customer_id тоже не попал бы. Именно поэтому INNER JOIN называют «пересечением»: результат — строки, которые присутствуют и в A, и в B.

Когда INNER JOIN — правильный выбор

INNER JOIN подходит, когда:

Вас интересуют только строки, для которых данные есть в обеих таблицах
Нужно «дополнить» строку информацией из другой таблицы (например, добавить имя к id)
Делаете аналитику: «заказы с деталями клиентов», «товары с их категориями»

INNER JOIN — выбор по умолчанию в большинстве аналитических запросов, именно поэтому ключевое слово INNER обычно опускают и пишут просто JOIN.

Типичные ошибки

1. Неуточнённые колонки с одинаковым именем:

SELECT id, name FROM customers JOIN orders ON ...;
-- ERROR: column reference "id" is ambiguous

Исправление: c.id, o.id.

2. JOIN без условия ON (декартово произведение):

-- Так нельзя — явная ошибка
SELECT * FROM customers JOIN orders;
-- ERROR: syntax error
-- В старом синтаксисе FROM customers, orders — не ошибка, но декартово произведение

3. Условие ON вместо WHERE: Это не ошибка при INNER JOIN — результат одинаковый. Но хороший стиль: ON описывает правило соединения таблиц, WHERE — фильтрацию результатов.

Краткий итог

INNER JOIN (или просто JOIN) возвращает только строки, для которых нашлось совпадение в обеих таблицах
Строки без пары в другой таблице исключаются из результата
Синтаксис: FROM A JOIN B ON A.key = B.key
Всегда уточняйте принадлежность колонок алиасом: c.name, o.amount
WHERE, GROUP BY, ORDER BY работают с JOIN так же, как с одной таблицей
NULL в ключевой колонке исключает строку из результата INNER JOIN

Что дальше

INNER JOIN оставляет только строки с совпадением. Но часто нужно видеть и строки без пары — например, всех клиентов, в том числе тех, кто ещё ничего не купил. Для этого существует LEFT JOIN — разберём его в следующем уроке.

INNER JOIN: пересечение таблиц