15.2.13.2 JOIN Clause — MySQL 8.4

15.2.13.2 Оператор JOIN

MySQL поддерживает следующий синтаксис для части в операторах SELECT и операторах множественной табличной DELETE и UPDATE:

table_references:
    escaped_table_reference [, escaped_table_reference] ...

escaped_table_reference: {
    table_reference
  | { OJ table_reference }
}

table_reference: {
    table_factor
  | joined_table
}

table_factor: {
    tbl_name [PARTITION (partition_names)]
        [[AS] alias] [index_hint_list]
  | [LATERAL] table_subquery [AS] alias [(col_list)]
  | ( table_references )
}

joined_table: {
    table_reference {[INNER | CROSS] JOIN | STRAIGHT_JOIN} table_factor [join_specification]
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN table_factor
}

join_specification: {
    ON search_condition
  | USING (join_column_list)
}

join_column_list:
    column_name[, column_name] ...

index_hint_list:
    index_hint[ index_hint] ...

index_hint: {
    USE {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
  | {IGNORE|FORCE} {INDEX|KEY}
      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
}

index_list:
    index_name [, index_name] ...

Ссылка на таблицу также известна как выражение соединения.

Ссылка на таблицу (когда она относится к разграниченной таблице) может содержать оператор PARTITION, включающий список разделимых запятыми партиций, подпартиций или обоих. Этот параметр следует за именем таблицы и предшествует любому объявлению псевдонима. Эффект этого параметра заключается в том, что строки выбираются только из перечисленных партиций или подпартиций. Любые партиции или подпартиции, не указанные в списке, игнорируются. Более подробную информацию и примеры см. в Разделе 26.5, «Выбор партиций».

Синтаксис table_factor расширен в MySQL по сравнению со стандартным SQL. Стандарт допускает только table_reference, а не список их в паре скобок.

Это консервативное расширение, если каждая запятая в списке элементов table_reference рассматривается как эквивалент внутреннему соединению. Например:

SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
                 ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

эквивалентно:

SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
                 ON (t2.a = t1.a AND t3.b = t1.b AND t4.c = t1.c)

В MySQL JOIN, CROSS JOIN и INNER JOIN являются синтаксическими эквивалентами (они могут заменять друг друга). В стандартном SQL они не эквивалентны. INNER JOIN используется с оператором ON, CROSS JOIN - в противном случае.

В общем, скобки можно игнорировать в выражениях соединения, содержащих только внутренние соединения. MySQL также поддерживает вложенные соединения. См. Раздел 10.2.1.8, «Оптимизация вложенных соединений».

Указатели индексов могут быть заданы для влияния на то, как оптимизатор MySQL использует индексы. Более подробную информацию см. в Разделе 10.9.4, «Указатели индексов». Указатели оптимизатора и системная переменная optimizer_switch - это другие способы влияния на использование оптимизатором индексов. См. Раздел 10.9.3, «Указатели оптимизатора» и Раздел 10.9.2, «Переключаемые оптимизации».

В следующем списке описаны общие факторы, которые следует учитывать при написании соединений:

Ссылку на таблицу можно присвоить псевдоним с помощью tbl_name AS alias_name или tbl_name alias_name:

SELECT t1.name, t2.salary
  FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

SELECT t1.name, t2.salary
  FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;

table_subquery также известна как выведенная таблица или подзапрос в операторе FROM. См. Раздел 15.2.15.8, «Выведенные таблицы». Такие подзапросы должны включать псевдоним, чтобы присвоить имени таблицы результат подзапроса, и могут необязательно включать список имен столбцов таблицы в скобках. Следующий пример тривиален:
```
SELECT * FROM (SELECT 1, 2, 3) AS t1;
```
Максимальное количество таблиц, которые можно использовать в одном соединении, составляет 61. Это включает соединение, обрабатываемое путем объединения выведенных таблиц и представлений в операторе FROM в основной блок запроса (см. Раздел 10.2.2.4, «Оптимизация выведенных таблиц, ссылок на представления и общих табличных выражений с объединением или материализацией»).
INNER JOIN и , (запятая) семантически эквивалентны в отсутствие условия соединения: оба создают декартово произведение между указанными таблицами (то есть, каждая строка в первой таблице соединяется с каждой строкой во второй таблице).

Однако приоритет оператора запятой ниже, чем у операторов INNER JOIN, CROSS JOIN, LEFT JOIN и так далее. Если вы смешиваете соединения с запятыми с другими типами соединений при наличии условия соединения, может возникнуть ошибка типа Unknown column 'col_name' in 'on clause'. Информация о решении этой проблемы приведена позже в этом разделе.
search_condition, используемый с ON, - это любое условное выражение, которое можно использовать в операторе WHERE. Обычно оператор ON служит для условий, определяющих способ соединения таблиц, а оператор WHERE ограничивает строки, которые следует включать в результирующий набор.
Если для правой таблицы нет соответствующей строки в части ON или USING в LEFT JOIN, используется строка со всеми столбцами, установленными на NULL. Вы можете использовать этот факт для поиска строк в таблице, у которых нет аналогов в другой таблице:
```
SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;
```
Этот пример находит все строки в left_tbl со значением id, отсутствующим в right_tbl (то есть, все строки в left_tbl без соответствующей строки в right_tbl). См. Раздел 10.2.1.9, «Оптимизация внешних соединений».
Оператор USING(join_column_list) называет список столбцов, которые должны существовать в обеих таблицах. Если таблицы a и b обе содержат столбцы c1, c2 и c3, следующее соединение сравнивает соответствующие столбцы из двух таблиц:
```
a LEFT JOIN b USING (c1, c2, c3)
```
Объединение двух таблиц определяется как семантически эквивалентное внутреннему соединению или внешнему соединению с оператором USING, который называет все столбцы, существующие в обеих таблицах.
RIGHT JOIN работает аналогично LEFT JOIN. Для сохранения портативности кода между базами данных рекомендуется использовать LEFT JOIN вместо RIGHT JOIN.
Синтаксис { OJ ... }, показанный в описании синтаксиса соединения, существует только для совместимости с ODBC. Фигурные скобки в синтаксисе должны быть написаны буквально; они не представляют собой метасинтаксис, как используется в других местах описаний синтаксиса.
```
SELECT left_tbl.*
    FROM { OJ left_tbl LEFT OUTER JOIN right_tbl
           ON left_tbl.id = right_tbl.id }
    WHERE right_tbl.id IS NULL;
```
Вы можете использовать другие типы соединений в { OJ ... }, такие как INNER JOIN или RIGHT OUTER JOIN. Это помогает в обеспечении совместимости с некоторыми сторонними приложениями, но не является официальным синтаксисом ODBC.
STRAIGHT_JOIN аналогичен JOIN, за исключением того, что левая таблица всегда считывается до правой таблицы. Это может использоваться для тех (небольших) случаев, для которых оптимизатор соединений обрабатывает таблицы в неэффективном порядке.

Некоторые примеры соединений:

SELECT * FROM table1, table2;

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

SELECT * FROM table1 LEFT JOIN table2 USING (id);

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
  LEFT JOIN table3 ON table2.id = table3.id;

Естественные соединения и соединения с USING, включая варианты внешних соединений, обрабатываются в соответствии со стандартом SQL:2003.

Избыточные столбцы в соединении NATURAL не отображаются. Рассмотрим этот набор операторов:
```
CREATE TABLE t1 (i INT, j INT);
CREATE TABLE t2 (k INT, j INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
SELECT * FROM t1 NATURAL JOIN t2;
SELECT * FROM t1 JOIN t2 USING (j);
```
В первом операторе SELECT столбец j присутствует в обеих таблицах и, следовательно, становится столбцом соединения, поэтому, согласно стандартному SQL, он должен отображаться только один раз в выводе, а не дважды. Аналогично, во втором операторе SELECT столбец j указан в части USING и должен отображаться только один раз в выводе, а не дважды.

Таким образом, операторы генерируют следующий вывод:
```
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
```
Удаление избыточных столбцов и упорядочение столбцов происходит в соответствии со стандартным SQL, что приводит к такому порядку отображения:
- Сначала объединенные общие столбцы двух соединенных таблиц, в порядке их появления в первой таблице
- Затем столбцы, уникальные для первой таблицы, в порядке их появления в этой таблице
- В-третьих, столбцы, уникальные для второй таблицы, в порядке их появления в этой таблице
Одиночный результирующий столбец, заменяющий два общих столбца, определяется с помощью операции coalesce. То есть, для двух t1.a и t2.a результирующий столбец соединения a определяется как a = COALESCE(t1.a, t2.a), где:
```
COALESCE(x, y) = (CASE WHEN x IS NOT NULL THEN x ELSE y END)
```
Если операция соединения — любая другая, столбцы результата соединения представляют собой конкатенацию всех столбцов соединенных таблиц.

Следствием определения объединенных столбцов является то, что для внешних соединений объединенный столбец содержит значение не-NULL столбца, если один из двух столбцов всегда NULL. Если ни один из столбцов, ни оба столбца не NULL, оба общих столбца имеют одинаковое значение, поэтому не имеет значения, какой из них выбран в качестве значения объединенного столбца. Простой способ интерпретировать это — предположить, что объединенный столбец внешнего соединения представлен общим столбцом внутренней таблицы JOIN. Предположим, что таблицы t1(a, b) и t2(a, c) имеют следующее содержимое:
```
t1    t2
----  ----
1 x   2 z
2 y   3 w
```
Тогда для этого соединения столбец a содержит значения t1.a:
```
mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | x    | NULL |
|    2 | y    | z    |
+------+------+------+
```
В отличие от этого, для этого соединения столбец a содержит значения t2.a.
```
mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
+------+------+------+
| a    | c    | b    |
+------+------+------+
|    2 | z    | y    |
|    3 | w    | NULL |
+------+------+------+
```
Сравните эти результаты с иначе эквивалентными запросами с JOIN ... ON:
```
mysql> SELECT * FROM t1 LEFT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a    | b    | a    | c    |
+------+------+------+------+
|    1 | x    | NULL | NULL |
|    2 | y    |    2 | z    |
+------+------+------+------+
```
```
mysql> SELECT * FROM t1 RIGHT JOIN t2 ON (t1.a = t2.a);
+------+------+------+------+
| a    | b    | a    | c    |
+------+------+------+------+
|    2 | y    |    2 | z    |
| NULL | NULL |    3 | w    |
+------+------+------+------+
```
Оператор USING может быть переписан как оператор ON, который сравнивает соответствующие столбцы. Однако, хотя USING и ON похожи, они не совсем одинаковы. Рассмотрим следующие два запроса:
```
a LEFT JOIN b USING (c1, c2, c3)
a LEFT JOIN b ON a.c1 = b.c1 AND a.c2 = b.c2 AND a.c3 = b.c3
```
Что касается определения строк, удовлетворяющих условию соединения, оба соединения семантически идентичны.

Что касается определения столбцов для отображения в расширении SELECT *, два соединения не семантически идентичны. Соединение USING выбирает объединенное значение соответствующих столбцов, тогда как соединение ON выбирает все столбцы из всех таблиц. Для соединения USING, SELECT * выбирает эти значения:
```
COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3)
```
Для соединения ON, SELECT * выбирает эти значения:
```
a.c1, a.c2, a.c3, b.c1, b.c2, b.c3
```
При внутреннем соединении COALESCE(a.c1, b.c1) эквивалентно либо a.c1, либо b.c1, поскольку оба столбца имеют одинаковое значение. При внешнем соединении (например, LEFT JOIN) один из двух столбцов может быть NULL. Этот столбец опускается из результата.
Оператор ON может ссылаться только на свои операнды.

Пример:
```
CREATE TABLE t1 (i1 INT);
CREATE TABLE t2 (i2 INT);
CREATE TABLE t3 (i3 INT);
SELECT * FROM t1 JOIN t2 ON (i1 = i3) JOIN t3;
```
Оператор завершается ошибкой Unknown column 'i3' in 'on clause', потому что i3 — это столбец в t3, который не является операндом оператора ON. Для обработки соединения перепишите оператор следующим образом:
```
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
```
JOIN имеет более высокий приоритет, чем оператор запятой (,), поэтому выражение соединения t1, t2 JOIN t3 интерпретируется как (t1, (t2 JOIN t3)), а не как ((t1, t2) JOIN t3). Это влияет на операторы, использующие оператор ON, поскольку этот оператор может ссылаться только на столбцы в операндах соединения, а приоритет влияет на интерпретацию этих операндов.

Пример:
```
CREATE TABLE t1 (i1 INT, j1 INT);
CREATE TABLE t2 (i2 INT, j2 INT);
CREATE TABLE t3 (i3 INT, j3 INT);
INSERT INTO t1 VALUES(1, 1);
INSERT INTO t2 VALUES(1, 1);
INSERT INTO t3 VALUES(1, 1);
SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);
```
JOIN имеет приоритет над оператором запятой, поэтому операнды оператора ON — t2 и t3. Поскольку t1.i1 не является столбцом ни в одном из операндов, результатом является ошибка Unknown column 't1.i1' in 'on clause'.

Для обработки соединения используйте один из следующих способов:
- Явно сгруппируйте первые две таблицы с помощью скобок, чтобы операнды оператора ON были (t1, t2) и t3:
```
SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);
```
- Избегайте использования оператора запятой и используйте JOIN вместо него:
```
SELECT * FROM t1 JOIN t2 JOIN t3 ON (t1.i1 = t3.i3);
```
Такая же интерпретация приоритета применяется и к операторам, которые смешивают оператор запятой с INNER JOIN, CROSS JOIN, LEFT JOIN и RIGHT JOIN, все из которых имеют более высокий приоритет, чем оператор запятой.
Расширение MySQL по сравнению со стандартом SQL:2003 заключается в том, что MySQL позволяет вам квалифицировать общие (объединенные) столбцы соединений NATURAL или USING, в то время как стандарт этого не допускает.