13.2.9.2 JOIN Clause — MySQL 5.7

13.2.9.2 Оператор JOIN

MySQL поддерживает следующий синтаксис для части table_references в операторах 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]
  | table_subquery [AS] alias
  | ( table_references )
}

joined_table: {
    table_reference [INNER | CROSS] JOIN table_factor [join_specification]
  | table_reference STRAIGHT_JOIN table_factor
  | table_reference STRAIGHT_JOIN table_factor ON search_condition
  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_specification
  | table_reference NATURAL [{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, включая список разделённых запятыми разделов, подразделов или обоих. Этот вариант следует за именем таблицы и предшествует любому объявлению псевдонима. В результате этого варианта строки выбираются только из указанных разделов или подразделов. Разделы или подразделы, не указанные в списке, игнорируются. Дополнительную информацию и примеры см. в разделе 22.5 «Выбор разделов».

Синтаксис table_factor в MySQL расширен по сравнению со стандартным SQL. Стандартный 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 также поддерживает вложенные соединения. См. раздел 8.2.1.7 «Оптимизация вложенных соединений».

Можно указать подсказки индексов, чтобы повлиять на то, как оптимизатор MySQL использует индексы. Дополнительную информацию см. в разделе 8.9.4 «Подсказки индексов». Другие способы влияния на использование оптимизатором индексов — это подсказки оптимизатора и системная переменная optimizer_switch. См. раздел 8.9.3 «Подсказки оптимизатора» и раздел 8.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. См. раздел 13.2.10.8 «Производные таблицы». Такие подзапросы должны включать псевдоним, чтобы присвоить подзапросу имя таблицы. Приведён тривиальный пример:
```
SELECT * FROM (SELECT 1, 2, 3) AS t1;
```
Максимальное количество таблиц, на которые можно ссылаться в одном соединении, равно 61. Это включает соединение, обрабатываемое с помощью объединения производных таблиц и представлений в операторе FROM в блок внешнего запроса (см. раздел 8.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). См. раздел 8.2.1.8 «Оптимизация внешних соединений».
Оператор 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, что приводит к такому порядку отображения:
- Во-первых, объединённые общие столбцы двух соединённых таблиц в порядке их появления в первой таблице
- Во-вторых, столбцы, уникальные для первой таблицы, в порядке их появления в этой таблице
- В-третьих, столбцы, уникальные для второй таблицы, в порядке их появления в этой таблице
Единственный столбец результата, который заменяет два общих столбца, определяется с помощью операции объединения. То есть, для двух 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 соединений, тогда как стандарт этого не допускает.