re:re:actable
Урок 013 минуты чтения

Что такое JOIN

На этой странице
Кратко

JOIN соединяет строки двух наборов по условию ON, формируя одну строку результата из пары подходящих строк. Основные виды: INNER JOIN — только пары с совпадением; LEFT JOIN — все строки левой таблицы, справа NULL, если пары нет; RIGHT JOIN — зеркально; FULL JOIN — все строки обеих сторон; CROSS JOIN — все комбинации. Логически JOIN — это декартово произведение, отфильтрованное условием, а физически СУБД выбирает алгоритм: nested loop, hash join или merge join. Количество строк результата зависит от кардинальности связи: при one-to-many строка «одной» стороны повторяется для каждой строки «многих».

Вопрос

Что такое JOIN и какие виды соединений бывают?

Пример данных

users                  orders
id | name              id | user_id | total
1  | Anna              10 | 1       | 500
2  | Ivan              11 | 1       | 300
3  | Olga              12 | 2       | 700
                       13 | NULL    | 100

INNER JOIN

SELECT u.name, o.id AS order_id, o.total
FROM users u
JOIN orders o ON o.user_id = u.id;    -- JOIN = INNER JOIN
Anna | 10 | 500
Anna | 11 | 300
Ivan | 12 | 700

Olga без заказов и заказ без пользователя не попали.

LEFT JOIN

SELECT u.name, o.id AS order_id
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;
Anna | 10
Anna | 11
Ivan | 12
Olga | NULL

Виды JOIN

ВидЧто возвращает
INNER JOINТолько совпавшие пары
LEFT JOINВсе слева + пары справа или NULL
RIGHT JOINВсе справа + пары слева или NULL
FULL JOINВсе с обеих сторон
CROSS JOINВсе комбинации
LATERALПодзапрос справа видит столбцы левой строки

Несколько JOIN

SELECT o.id, u.name, p.title, oi.quantity
FROM orders o
JOIN users u        ON u.id = o.user_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
WHERE o.created_at >= now() - interval '7 days';

ON и USING

JOIN orders o ON o.user_id = u.id          -- любое условие
JOIN orders USING (user_id)                -- если столбцы называются одинаково

NATURAL JOIN соединяет по всем одноимённым столбцам автоматически — опасно: новый столбец created_at в обеих таблицах молча изменит запрос.

Как JOIN выполняется физически

АлгоритмКогда выгоден
Nested LoopМало строк снаружи + индекс на внутренней стороне
Hash JoinБольшие наборы, условие равенства, без подходящего индекса
Merge JoinОба входа отсортированы по ключу соединения

Какой выбран — видно в EXPLAIN.

Частые ошибки

  • Забыть условие ON или ошибиться в нём — получается декартово произведение или неверные пары.
  • Фильтр по правой таблице LEFT JOIN в WHERE — внешнее соединение превращается во внутреннее.
  • Не ожидать размножения строк при one-to-many (суммы по «одной» стороне считаются многократно).
  • Не индексировать внешние ключи, по которым соединяют.

Что важно помнить

  • JOIN = пары строк, удовлетворяющие ON.
  • INNER отбрасывает строки без пары, LEFT сохраняет левую сторону.
  • Количество строк после JOIN определяется связью, а не размером левой таблицы.

Мини-шпаргалка

  • JOIN ... ON a.x = b.y.
  • LEFT JOIN — сохранить все слева.
  • USING (col) — одинаковые имена.
  • Без NATURAL JOIN.
  • Алгоритмы: nested loop, hash, merge.

Документация

PostgreSQL: Joined Tables, PostgreSQL Tutorial: Joins.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам