Перейти к основному содержимому

Алиасы, JOIN и объединение таблиц

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Перед чтением: Операторы — операнды и приоритеты; здесь — в ON и условиях соединения.

Алиасы, JOIN и объединение таблиц​

Данные редко лежат в одной таблице. JOIN собирает строки по ключу связи; UNION склеивает результаты с одинаковой структурой столбцов.

Сначала: SELECT, подзапросы вместо join: Подзапросы, EXISTS и IN.

Перед сложным JOIN полезно пройти пять шагов формулировки запроса (столбцы → таблицы → соединение → фильтр → порядок вывода).

Как составить запрос с соединениями​

На шагах 2–3 общей схемы (см. внутреннее устройство БД) для JOIN держите в голове:

  1. Якорная таблица — сущность, от которой строите отчёт (часто "главная" в FROM).
  2. Ключ связи — customer_id → customers.id, не "по совпадению имён" без FK.
  3. Тип JOIN — INNER оставляет только совпавшие строки; LEFT сохраняет все строки левой таблицы.
  4. Условие в ON — связь таблиц; бизнес-фильтры — в WHERE, чтобы не превратить LEFT JOIN в INNER по ошибке.

Если строк стало "слишком много", проверьте лишние таблицы в FROM и отсутствие неявного CROSS JOIN (забытый ON).

Алиасы​

★ Алиасы (AS) используются для временного переименования таблиц или столбцов в запросе SQL. Они делают запросы более читаемыми и позволяют избежать конфликтов имён.

Пример для столбцов (AS employee_name):

SELECT 
    name AS employee_name,
    salary * 12 AS annual_salary
FROM employees;

Немного английского - employee это работник, employer работодатель.

Пример для таблиц:

SELECT 
    e.name,
    d.name AS department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.id;

Здесь мы видим, что таблица employees получила алиас (псевдоним) e, а departments получила алиас d. Это позволяет обращаться к ним не традиционно

<имя таблицы>.<имя столбца>

а так:

<алиас>.<имя столбца>.

Если имя столбца длинное и страшное, к примеру, UltraHardMegaBigNameOfThisHugeDepartment, можно уменьшить, используя алиас "u" - и запрос станет более читабельным.

В тексте SELECT пишут выше FROM, но логический порядок (упрощённо): FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. Алиас из FROM employees AS e доступен в SELECT e.name этого же запроса.

Ключевые моменты:

  • алиасы действуют только в рамках одного запроса;
  • алиас из FROM виден в SELECT, WHERE, JOIN того же запроса;
  • переставлять блоки в файле нельзя: без FROM employees e выражение SELECT e.name не скомпилируется;
  • ключевое слово AS можно опустить:
SELECT e.name FROM employees e

Объединения (JOIN)​

Что такое JOIN?​

★ Объединения (JOIN) используются для объединения данных из двух и более таблиц на основе связанных столбцов.

Play ITЗагрузка интерактивного демо…

JOIN на одной картинке (смысл типов)​

У двух таблиц A (слева в FROM) и B (подключается через JOIN) удобно думать о пересечении по ключу:

ТипЧто попадает в результатОбразно
INNER JOINТолько строки с парой в обеих таблицахПересечение кругов A и B
LEFT JOINВсе строки A; для "сирот" справа — NULLВесь круг A
RIGHT JOINВсе строки B; для "сирот" слева — NULLВесь круг B
FULL OUTER JOINВсе строки A и B; пропуски — NULLОба круга целиком

Разбор на числах и SQL — в четырёх JOIN на одном примере.

Представим, что нам надо объединить таблицы employees и departments:

image-2.png

У нас есть несколько вариантов объединения. Практически это нужно, к примеру, когда у вас данные раскиданы по нескольким таблицам, а вы хотите получить какой-то общий результат с данными из этих разных таблиц.

Основные типы JOIN — INNER, OUTER (LEFT, RIGHT, FULL), CROSS.

Связь в схеме задаётся внешним ключом; в запросе строки "склеивают" по равенству FK и PK родителя, например orders.customer_id = customers.customer_id. Теория ключей и кардинальности — Реляционная модель и ER.

ТипКакие строки в результатеКогда удобен
INNER JOINТолько пары с совпадением в обеих таблицахНужны только "связанные" факты (заказ с известным клиентом)
LEFT JOINВсе строки левой таблицы; справа NULL, если пары нет"Все клиенты и их заказы, если есть"
RIGHT JOINВсе строки правой таблицы; слева NULL, если пары нетТо же, что LEFT, если удобнее считать правую таблицу "главной"
FULL OUTER JOINОбъединение LEFT и RIGHTСверка двух списков "кто есть только слева / только справа"
CROSS JOINДекартово произведение (каждая со каждой)Комбинации справочников, плотные отчёты — разбор
Практика

В отчётах чаще всего хватает INNER (только связанные строки) и LEFT (сохранить "главную" таблицу целиком).

RIGHT в PostgreSQL эквивалентен LEFT с переставленными таблицами.

Готовые запросы на схеме магазина — сумма заказа, клиенты без заказов, топ товаров — с построчным разбором: SQL — реальные кейсы.

Четыре основных JOIN на одном примере​

Чтобы сравнить типы соединения, возьмём две маленькие таблицы с общим столбцом key. Левая — та, что указана первой в FROM (здесь table_a), правая — вторая (table_b).

Таблица A (слева в запросе):

keyval_a
1A1
2A2
3A3

Таблица B (справа):

keyval_b
1B1
2B2
4B3

Совпадают ключи 1 и 2. Ключ 3 есть только в A, ключ 4 — только в B. Условие связи во всех запросах ниже — ON a.key = b.key.

Тип JOINЧто попадает в результатСтрок в примере
INNERТолько совпавшие пары2
LEFTВсе строки A; для "сирот" справа — NULL3
RIGHTВсе строки B; для "сирот" слева — NULL3
FULL OUTERВсе строки из A и B; пропуски с обеих сторон — NULL4
CROSSКаждая строка A с каждой строкой B; ON не нужен9 (3×3) — подробнее

INNER JOIN — только пересечение​

Возвращаются только строки, для которых нашлась пара в обеих таблицах. Ключи 3 и 4 отбрасываются.

SELECT a.key, a.val_a, b.val_b
FROM table_a AS a
INNER JOIN table_b AS b ON a.key = b.key;
keyval_aval_b
1A1B1
2A2B2

LEFT JOIN — вся левая таблица​

LEFT JOIN (то же, что LEFT OUTER JOIN) сохраняет каждую строку из левой таблицы. Если справа пары нет, столбцы B заполняются NULL.

SELECT a.key, a.val_a, b.val_b
FROM table_a AS a
LEFT JOIN table_b AS b ON a.key = b.key;
keyval_aval_b
1A1B1
2A2B2
3A3NULL

Типичный сценарий — "все клиенты и их заказы, если заказы есть": главная сущность слева, детали справа.

RIGHT JOIN — вся правая таблица​

RIGHT JOIN зеркален LEFT: на месте "главной" оказывается правая таблица. Строка с ключом 4 есть только в B, поэтому val_a будет NULL.

SELECT a.key, a.val_a, b.val_b
FROM table_a AS a
RIGHT JOIN table_b AS b ON a.key = b.key;
keyval_aval_b
1A1B1
2A2B2
4NULLB3

На практике тот же эффект даёт LEFT JOIN с переставленными таблицами — RIGHT пишут реже.

FULL OUTER JOIN — объединение LEFT и RIGHT​

FULL OUTER JOIN возвращает все строки из обеих таблиц: совпадения склеиваются, "сироты" с любой стороны остаются с NULL на противоположной.

SELECT a.key, a.val_a, b.val_b
FROM table_a AS a
FULL OUTER JOIN table_b AS b ON a.key = b.key;
keyval_aval_b
1A1B1
2A2B2
3A3NULL
4NULLB3

Удобно для сверки двух списков (например, "кто есть только в справочнике A / только в B"). Поддержка FULL OUTER зависит от СУБД — см. примечание в разделе FULL JOIN ниже.

Как запомнить

INNER — пересечение по ключу.
LEFT — "не потерять" левую таблицу.
RIGHT — то же для правой.
FULL — LEFT и RIGHT вместе, без потери строк с любой стороны.
CROSS — каждая строка слева с каждой справа, без ON — пример.

CROSS JOIN на одном примере​

CROSS JOIN строит декартово произведение двух таблиц. Декартово произведение — это когда каждая строка (в теории реляционных БД её называют кортежем) первой таблицы сочетается с каждой строкой второй. Общий ключ, внешний ключ и условие ON здесь не участвуют — СУБД просто перебирает все пары.

INNER, LEFT, RIGHT и FULL сначала ищут совпадение столбцов в ON. CROSS JOIN этого шага не делает. Подробнее про место операции в реляционной алгебре и в списке операций над отношениями.

Размер результата. Если в таблице A — N строк, а в таблице B — M строк, в ответе будет ровно N × M строк. Две строки и три строки дают шесть комбинаций, тысяча и тысяча — уже миллион.

Комбо-обед (бургеры и напитки)​

Два независимых справочника. Между ними нет столбца связи — нам нужен полный каталог пар "блюдо + напиток".

Таблица burgers

burger_name
Чизбургер
Чикенбургер

Таблица drinks

drink_name
Кола
Сок
Чай
SELECT b.burger_name, d.drink_name
FROM burgers AS b
CROSS JOIN drinks AS d;

Результат

burger_namedrink_name
ЧизбургерКола
ЧизбургерСок
ЧизбургерЧай
ЧикенбургерКола
ЧикенбургерСок
ЧикенбургерЧай

Получилось 2 × 3 = 6 строк. Первая строка burgers (Чизбургер) сочетается с каждым напитком, затем то же для Чикенбургера.

Те же table_a и table_b, но без ON​

Возьмём таблицы из четырёх JOIN выше. Для INNER / LEFT / RIGHT / FULL мы писали ON a.key = b.key. В CROSS JOIN условие убираем:

SELECT a.key AS key_a, a.val_a, b.key AS key_b, b.val_b
FROM table_a AS a
CROSS JOIN table_b AS b;

Результат (фрагмент)

key_aval_akey_bval_b
1A11B1
1A12B2
1A14B3
2A21B1
…………
3A34B3

Всего 3 × 3 = 9 строк. Отбор по совпадению ключей не выполняется — в ответе каждая строка table_a стоит рядом с каждой строкой table_b.

INNER JOIN​

★ INNER JOIN (внутреннее соединение) – возвращает только строки, где есть соответствие в обеих таблицах:

SELECT 
    e.name AS employee_name,
    d.name AS department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Пример:

image-3.png

Есть также внешние соединения (OUTER JOIN). Выделяют LEFT OUTER JOIN, RIGHT OUTER JOIN и FULL OUTER JOIN.

LEFT JOIN​

★ LEFT JOIN или LEFT OUTER JOIN (Левое соединение) возвращает все строки из левой таблицы и соответствующие из правой. Тот же смысл на минимальном примере — в блоке LEFT JOIN на table_a / table_b.

Если соответствия нет – NULL:

SELECT 
    e.name AS employee_name,
    d.name AS department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

Пример:

image-4.png

RIGHT JOIN​

★ RIGHT JOIN или RIGHT OUTER JOIN (Правое соединение) возвращает все строки из правой таблицы и соответствующие из левой. Если соответствия нет – NULL.

SELECT 
    e.name AS employee_name,
    d.name AS department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Пример:

image-5.png

FULL JOIN​

★ FULL JOIN или FULL OUTER JOIN (Полное соединение) возвращает все строки из обеих таблиц. Если соответствия нет – NULL:

Таблица результата на тех же table_a / table_b, что в примере четырёх JOIN.

SELECT 
    e.name AS employee_name,
    d.name AS department_name
FROM employees e
FULL JOIN departments d ON e.department_id = d.id;

Пример:

image-6.png

Диалект: FULL OUTER JOIN — PostgreSQL, SQL Server, Oracle. В MySQL долго обходили через UNION LEFT+RIGHT; с 8.0.14+ есть нативная поддержка — проверяйте версию.

CROSS JOIN​

★ CROSS JOIN (декартово произведение) соединяет каждую строку левой таблицы с каждой строкой правой. Условие ON не пишут — СУБД возвращает полный набор комбинаций.

Числа и таблицы по шагам — в блоке CROSS JOIN на одном примере.

Обычные JOIN связывают таблицы по внешнему ключу (заказ хранит customer_id, сотрудник — department_id). CROSS JOIN из двух независимых списков строит сетку всех пар.

  • каталог вариантов товара (размер × цвет);
  • матрица "дата × магазин" для отчёта, где должны быть и дни без продаж;
  • разворот массива в JSON через jsonb_array_elements — см. практикум по JSONB.
SELECT
    e.name AS employee_name,
    d.name AS department_name
FROM employees AS e
CROSS JOIN departments AS d;

Здесь каждый сотрудник попадает в пару с каждым отделом, хотя в employees уже есть department_id. Для отчёта "сотрудник и его отдел" подойдут INNER JOIN или LEFT JOIN по ключу с ON e.department_id = d.id.

Альтернативный синтаксис​

Декартово произведение можно записать через запятую в FROM — так делали в раннем SQL:

SELECT b.burger_name, d.drink_name
FROM burgers AS b, drinks AS d;

Результат совпадает с CROSS JOIN. При явной записи CROSS JOIN сразу видно, что пары строк собраны намеренно, без пропущенного ON.

Когда CROSS JOIN применяют на практике​

Комбинации справочников

  • размеры одежды × палитра цветов для карточки товара;
  • бургеры × напитки для меню комбо;
  • участники турнира × участники (каждый с каждым) для сетки матчей.

Плотный отчёт (data dense)

В аналитике data dense — отчёт, где есть строка на каждую комбинацию измерений, даже если фактических данных не было. Без этого INNER JOIN к таблице продаж "выкинет" дни и магазины с нулевой выручкой.

Сначала CROSS JOIN строит сетку "дата × магазин", затем LEFT JOIN по тому же примеру подставляет суммы из фактов, а COALESCE и работа с NULL заменяют пропуски на 0.

SELECT
    c.report_date,
    s.store_name,
    COALESCE(sales.amount, 0) AS amount
FROM calendar AS c
CROSS JOIN stores AS s
LEFT JOIN daily_sales AS sales
    ON sales.store_id = s.id
   AND sales.sale_date = c.report_date
ORDER BY c.report_date, s.store_name;

Календарь calendar часто генерируют функцией generate_series в PostgreSQL — примеры в шпаргалке по функциям.

Мини-пример плотного отчёта​

Два дня и два магазина. Продажа была только в один день в одном магазине.

report_datestore_nameamount
2025-06-01Центр1500
2025-06-01Север0
2025-06-02Центр0
2025-06-02Север0

Без CROSS JOIN в выборке осталась бы одна строка с продажей.

Тестовые данные

  • пары true / false для набора флагов;
  • последовательность чисел через generate_series × справочник статусов;
  • проверка всех веток бизнес-логики на маленьких таблицах перед выкладкой в прод.

Комбинации размеров и цветов​

SELECT sz.size_label, clr.color_name
FROM sizes AS sz
CROSS JOIN colors AS clr
ORDER BY sz.size_label, clr.color_name;

Три размера и четыре цвета дадут 12 строк — готовый список SKU для заведения карточек.

Неявный CROSS JOIN (частая ошибка)​

Две таблицы в FROM через запятую или JOIN без ON часто дают декартово произведение (в строгих СУБД — ошибку компиляции). Типичный опасный запрос:

-- Забыли ON — неявный CROSS JOIN
SELECT o.id, c.name
FROM orders AS o, customers AS c
WHERE o.total > 1000;

WHERE o.total > 1000 отбирает только дорогие заказы, но не связывает заказ с его клиентом. Сначала строятся все пары "заказ × клиент", потом фильтр по сумме — ответ неверный и может быть гигантским.

Правильная связь по ключу:

SELECT o.id, c.name
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id
WHERE o.total > 1000;

Условие связи таблиц держите в ON, бизнес-фильтры — в WHERE. Подробнее про ловушку WHERE после LEFT JOIN — в FAQ по SQL и в чек-листе соединений. Готовые запросы на схеме магазина — практикум shop_data.

Перегрузка сервера

CROSS JOIN на больших таблицах быстро исчерпывает память. Клиенты (10 000 строк) × заказы (100 000 строк) без условия связи — 1 000 000 000 строк в промежуточном результате.

Применяйте оператор к коротким справочникам или когда заранее прикидываете N × M. На этапе отладки смотрите план в EXPLAIN — см. шпаргалку типичных задач.

Эквисоединение и естественное соединение​

Эквисоединение (Equal Join) - соединение, в котором условие ON основано на равенстве значений в столбцах двух таблиц. INNER JOIN, LEFT JOIN и т.д. с условием ON t1.col = t2.col — это и есть эквисоединения. Подавляющее большинство JOIN в реальных запросах — эквисоединения.

Естественное соединение (NATURAL JOIN) - автоматически объединяет таблицы по всем одноимённым столбцам. Не требует явного указания условия ON. Если в обеих таблицах есть столбцы с одинаковыми именами (например, id, department_id), то NATURAL JOIN использует их для соединения по равенству. Столбец, по которому происходит соединение, отображается только один раз в результате.

На практике NATURAL JOIN редко используется, потому что легко ошибиться, запрос становится хрупким (изменение имени столбца может непреднамеренно повлиять на JOIN) и не видно сразу, по каким полям происходит соединение.

UNION​

Что такое UNION?​

Ключевое слово UNION позволяет выполнить один или несколько дополнительных запросов SELECT и добавить их результаты к исходному запросу. То есть, это SQL-оператор, который позволяет объединять результаты двух или более запросов SELECT в один результирующий набор. Это очень удобно, когда нужно собрать данные из разных таблиц или условий, но с одинаковой структурой.

UNION ALL — повторяющиеся строки включаются (объединяет результаты, сохраняя все строки, включая дубли (работает быстрее)).

UNION ALL

UNION — повторяющиеся строки исключаются (объединяет результаты, удаляя дубликаты (работает медленнее)).

UNION

Основное правило UNION - все объединяемые запросы должны возвращать одинаковое количество столбцов, и типы данных в соответствующих столбцах должны быть совместимыми.

Операция UNION отличается от операции JOIN. В результате операции UNION сцепляются результирующие наборы двух запросов. При этом операция UNION не создает отдельные строки для столбцов, полученных из двух таблиц. Операция JOIN сравнивает столбцы из двух таблиц и создает результирующие строки, которые состоят из столбцов из двух таблиц.

На что обращать внимание при использовании UNION?

  1. Совпадение количества и порядка столбцов. При использовании UNION, если в одном запросе 3 столбца, а в другом — 2, будет ошибка.
  2. Совместимость типов данных. Например, нельзя сложить число и строку: — ошибка.
SELECT id FROM table1 UNION SELECT name FROM table2
  1. Использование ORDER BY. Чтобы отсортировать итоговый результат, ORDER BY указывается в самом конце, после всех UNION.
  2. Производительность. UNION требует дополнительной работы — удаления дубликатов. Поэтому, если дубли не важны, лучше использовать UNION ALL.

Поэтому команду UNION нужно использовать тогда, когда нужно объединить данные из разных таблиц/запросов с одинаковой структурой, к примеру, при построении отчётов, где требуется выборка из нескольких источников или для создания сводных списков, например, "все клиенты", независимо от типа.

INTERSECT и EXCEPT​

INTERSECT — строки, присутствующие в обоих результатах (дубликаты убираются, как в UNION).

EXCEPT — строки из первого запроса, которых нет во втором. Порядок важен: A EXCEPT B ≠ B EXCEPT A.

-- Категории, в которых есть и товары, и заказы (упрощённый пример)
SELECT category FROM products
INTERSECT
SELECT category FROM products WHERE price > 1000;

-- Товары категории "Книги", которых нет среди дорогих (> 1500)
SELECT name FROM products WHERE category = 'Книги'
EXCEPT
SELECT name FROM products WHERE price > 1500;

Приоритет без скобок: сначала INTERSECT, затем UNION / EXCEPT слева направо. Для другого порядка используйте скобки:

(SELECT id FROM a UNION SELECT id FROM b)
INTERSECT
SELECT id FROM c;

В PostgreSQL из расширений "ALL" для INTERSECT / EXCEPT нет — только UNION ALL. Два NULL в одном столбце при UNION считаются равными при удалении дубликатов.

См. также​

Содержание