13.2.9.2. `JOIN` Синтаксис

MySQL поддерживает следующий JOIN синтаксисы для 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  | join_tabletable_factor:    tbl_name [PARTITION (partition_names)]         [[AS] alias] [index_hint_list]  | table_subquery [AS] alias  | ( table_references )join_table:    table_reference [INNER | CROSS] JOIN table_factor [join_condition]  | table_reference STRAIGHT_JOIN table_factor  | table_reference STRAIGHT_JOIN table_factor ON conditional_expr  | table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition  | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factorjoin_condition:    ON conditional_expr  | USING (column_list)index_hint_list:    index_hint [, index_hint] ...index_hint:    USE {INDEX|KEY}      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])  | IGNORE {INDEX|KEY}      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)  | FORCE {INDEX|KEY}      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)index_list:    index_name [, index_name] ...

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

В MySQL 5.6.2 и позже, ссылка на таблицу (когда это ссылается на разделенную таблицу) может содержать a PARTITION опция, включая список разделенных запятой значений разделов, подразделов, или обоих. Эта опция следует за именем таблицы и предшествует любому объявлению альтернативного имени. Эффект этой опции состоит в том, что строки выбираются только из перечисленных разделов или подразделов — другими словами, любые разделы или подразделы, не названные в списке, игнорируются Для получения дополнительной информации, видят Раздел 18.5, "Выбор Раздела".

Синтаксис table_factor расширяется по сравнению со Стандартом 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.13.9, "Вложенная Оптимизация Соединения").

Индексируйте подсказки, может быть определен, чтобы влиять, как оптимизатор MySQL использует, индексирует. Для получения дополнительной информации см. Раздел 13.2.9.3, "Индексируйте Синтаксис Подсказки".

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

Ссылка на таблицу может быть искажена, используя 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;

A table_subquery также известен как подзапрос в FROM пункт. Такие подзапросы должны включать псевдоним, чтобы дать результату подзапроса имя таблицы. Тривиальный пример следует; см. также Раздел 13.2.10.8, "Подзапросы в FROM Пункт".
```
SELECT * FROM (SELECT 1, 2, 3) AS t1;
```
INNER JOIN и , (запятая) семантически эквивалентна в отсутствие условия объединения: оба производят Декартово произведение между указанными таблицами (то есть, каждая строка в первой таблице соединяется с каждой строкой во второй таблице).

Однако, приоритет оператора запятой является меньше чем INNER JOIN, CROSS JOIN, LEFT JOIN, и так далее. Если Вы смешиваете соединения запятой с другими типами соединения, когда есть условие объединения, ошибка формы Unknown column 'col_name' in 'on clause' может произойти. Информация о контакте с этой проблемой дается позже в этом разделе.
conditional_expr используемый с ON любое условное выражение формы, которая может использоваться в a WHERE пункт. Обычно, следует использовать ON пункт для условий, которые определяют как к объединяющим таблицам, и WHERE пункт, чтобы ограничить, какие строки Вы хотите в наборе результатов.
Если нет никакой строки соответствия для правильной таблицы в ON или USING часть в a 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). Это принимает это right_tbl.id объявляется NOT NULL. См. Раздел 8.13.7,"LEFT JOIN и RIGHT JOIN Оптимизация".
USING(column_list) пункт называет список столбцов, которые должны существовать в обеих таблицах. Если таблицы a и b оба содержат столбцы c1, c2, и c3, следующее соединение сравнивает соответствующие столбцы от этих двух таблиц:
```
a LEFT JOIN b USING (c1,c2,c3)
```
NATURAL [LEFT] JOIN из двух таблиц определяется, чтобы быть семантически эквивалентным INNER JOIN или a LEFT JOIN с a 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;

Присоединитесь к Изменениям Обработки в MySQL 5.0.12

Отметить

Естественные соединения и соединения с USING, включая разновидности внешнего объединения, обрабатываются согласно стандарту SQL:2003. Цель состояла в том, чтобы выровнять синтаксис и семантику MySQL относительно NATURAL JOIN и JOIN ... USING согласно SQL:2003. Однако, эти изменения в обработке соединения могут привести к различным выходным столбцам для некоторых соединений. Кроме того, некоторые запросы, которые, казалось, работали правильно в более старых версиях (до 5.0.12) должны быть переписаны, чтобы выполнить стандарт.

У этих изменений есть пять основных аспектов:

Способ, из которого MySQL определяет столбцы результата NATURAL или USING операции соединения (и таким образом результат всего FROM пункт).
Расширение SELECT * и SELECT tbl_name.* в список выбранных столбцов.
Разрешение имен столбцов в NATURAL или USING соединения.
Преобразование NATURAL или USING соединения в JOIN ... ON.
Разрешение имен столбцов в ON условие a JOIN ... ON.

Следующий список обеспечивает больше детали, приблизительно несколько эффектов текущей обработки соединения против присоединяются к обработке в более старых версиях. Термин "ранее" означает "до MySQL 5.0.12."

Столбцы a NATURAL соединение или a USING соединение может отличаться от ранее. Определенно, избыточные выходные столбцы больше не появляются, и порядок столбцов для SELECT * расширение может отличаться до.

Рассмотрите этот набор операторов:
```
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);
```
Ранее, операторы, произведенные этот вывод:
```
+------+------+------+------+| i    | j    | k    | j    |+------+------+------+------+|    1 |    1 |    1 |    1 |+------+------+------+------++------+------+------+------+| i    | j    | k    | j    |+------+------+------+------+|    1 |    1 |    1 |    1 |+------+------+------+------+
```
В первом SELECT оператор, столбец j появляется в обеих таблицах и таким образом становится объединяющим столбцом, таким образом, согласно стандартному SQL, это должно появиться только однажды в выводе, не дважды. Точно так же во втором операторе SELECT, столбце j называется в USING пункт и должен появиться только однажды в выводе, не дважды. Но в обоих случаях, избыточный столбец не устраняется. Кроме того, порядок столбцов не корректен согласно стандартному SQL.

Теперь операторы производят этот вывод:
```
+------+------+------+| 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 V1 IS NOT NULL THEN V1 ELSE V2 END)
```
Если работа соединения является каким-либо другим соединением, столбцы результата соединения состоит из связи всех столбцов объединяемых таблиц. Это - то же самое как ранее.

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

Сравните эти результаты с иначе эквивалентными запросами с 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    |+------+------+------+------+
```
Ранее, a 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. Тот столбец будет опущен от результата.
Оценка многоканальных естественных соединений отличается очень важным способом, который влияет на результат NATURAL или USING соединения и это могут потребовать перезаписи запроса. Предположите, что у Вас есть три таблицы t1(a,b), t2(c,b), и t3(a,c) то, что каждый ссорится: t1(1,2), t2(10,2), и t3(7,10). Предположите также, что у Вас есть это NATURAL JOIN на этих трех таблицах:
```
SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3;
```
Ранее, левый операнд второго соединения, как полагали, был t2, тогда как это должно быть вложенное соединение (t1 NATURAL JOIN t2). В результате столбцы t3 проверяются на общие столбцы только в t2, и, если t3 имеет общие столбцы с t1, эти столбцы не используются в качестве столбцов объединения по эквивалентности. Таким образом, ранее, предыдущий запрос был преобразован к следующему объединению по эквивалентности:
```
SELECT ... FROM t1, t2, t3  WHERE t1.b = t2.b AND t2.c = t3.c;
```
То соединение пропускает еще один предикат объединения по эквивалентности (t1.a = t3.a). В результате это производит одну строку, не пустой результат, что это должно. Корректный эквивалентный запрос - это:
```
SELECT ... FROM t1, t2, t3  WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;
```
Если Вы требуете того же самого результата запроса в текущих версиях MySQL как в более старых версиях, переписываете естественное соединение как первое объединение по эквивалентности.
Ранее, оператор запятой (,) и JOIN у обоих был тот же самый приоритет, таким образом, выражение соединения t1, t2 JOIN t3 интерпретировался как ((t1, t2) JOIN t3). Теперь JOIN имеет более высокий приоритет, таким образом, выражение интерпретируется как (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);
```
Ранее, SELECT было законным из-за неявной группировки t1,t2 как (t1,t2). Теперь 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, у всех из которых теперь есть более высокий приоритет чем оператор запятой.
Ранее, ON пункт мог отнестись к столбцам в таблицах, названных с его правой стороны от него. Теперь 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;
```
Ранее, SELECT оператор был законным. Теперь оператор перестал работать с Unknown column 'i3' in 'on clause' ошибка, потому что i3 столбец в t3, который не является операндом ON пункт. Оператор должен быть переписан следующим образом:
```
SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);
```
Разрешение имен столбцов в NATURAL или USING соединения отличаются чем ранее. Для имен столбцов, которые являются вне FROM пункт, MySQL теперь обрабатывает надмножество запросов по сравнению с ранее. Таким образом, в случаях, когда MySQL, прежде выпущенный, ошибка, что некоторый столбец неоднозначен, запрос теперь, обрабатывается правильно. Это - то, вследствие того, что MySQL теперь обрабатывает общие столбцы NATURAL или USING соединения как единственный столбец, так, когда запрос обращается к таким столбцам, компилятор запросов, не рассматривают их как неоднозначных.

Пример:
```
SELECT * FROM t1 NATURAL JOIN t2 WHERE b > 1;
```
Ранее, этот запрос произвел бы ошибку ERROR 1052 (23000): Column 'b' in where clause is ambiguous. Теперь запрос приводит к корректному результату:
```
+------+------+------+| b    | c    | y    |+------+------+------+|    4 |    2 |    3 |+------+------+------+
```
Одно расширение MySQL по сравнению со стандартом SQL:2003 состоит в том, что MySQL позволяет Вам квалифицировать общие (объединенные) столбцы NATURAL или USING соединения (так же, как ранее), в то время как стандарт отвергает это.

13.2.9.2. JOIN Синтаксис

13.2.9.2. `JOIN` Синтаксис