SELECT ... JOIN子句

JOIN对于table_references部分SELECT语句以及多表DELETE和UPDATE语句， MySQL支持以下语法：

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 [(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选项，包括逗号分隔的分区，子分区或两者的列表。该选项在表名之后并在任何别名声明之前。此选项的作用是仅从列出的分区或子分区中选择行。列表中未命名的所有分区或子分区都将被忽略。有关更多信息和示例，请参见“分区选择”。

table_factor与标准SQL相比，MySQL 的语法得到了扩展。该标准仅接受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还支持嵌套连接。请参见“嵌套联接优化（JOIN）”。

可以指定索引提示来影响MySQL优化器如何使用索引。有关更多信息，请参见“索引提示”。优化器提示和optimizer_switch系统变量是影响优化器索引使用的其他方式。请参见“优化器提示”和“可切换的优化”。

下表描述了编写联接时要考虑的一般因素：

• 表引用可以使用或作为别名：tbl_name AS alias_nametbl_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子句中也称为派生表或子查询。请参见“派生表”。此类子查询必须包含别名以为子查询结果提供表名，并且可以选择在括号中包含表列名的列表。一个简单的示例如下：

  SELECT * FROM (SELECT 1, 2, 3) AS t1;
  

• 单个联接中最多可引用61个表。这包括通过将FROM子句中的派生表和视图合并到外部查询块中来处理的联接（请参见“优化派生表，视图引用，以及具有合并或实现的通用表表达式”）。

• 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）。请参见“外部联接优化”。

• 该子句命名两个表中必须存在的列的列表。如果表和两者都包含列，和，下面加入比较从两个表中对应的列：USING(join_column_list)abc1c2c3

  a LEFT JOIN b USING (c1, c2, c3)
  

• NATURAL[LEFT] JOIN两个表被定义为语义上等同于一个INNER JOIN或一个LEFT JOIN带有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语句中，column 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具有比逗号运算符（,）更高的优先级，因此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，所有这些都具有比逗号操作符更高的优先级。

• 与SQL：2003标准相比，MySQL的扩展是MySQL允许您限定NATURAL或USING联接的公共（成对）列，而标准不允许这样做。