A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:表引用可以是别名，方法是使用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 is also known as a derived table or subquery in the FROM clause. table_subquery在FROM子句中也称为派生表或子查询。See Section 13.2.11.8, “Derived Tables”. 请参阅第13.2.11.8节，“导出表”。Such subqueries must include an alias to give the subquery result a table name, and may optionally include a list of table column names in parentheses. 这样的子查询必须包含一个别名，以便为子查询结果提供一个表名，并且可以选择在括号中包含一个表列名列表。A trivial example follows:下面是一个简单的例子：

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

The maximum number of tables that can be referenced in a single join is 61. 在单个联接中可以引用的最大表数是61。This includes a join handled by merging derived tables and views in the FROM clause into the outer query block (see Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization”).这包括通过将FROM子句中的派生表和视图合并到外部查询块中来处理的联接（请参阅第8.2.2.4节“通过合并或物化优化派生表、视图引用和公共表表达式”）。

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).在没有联接条件的情况下，INNER JOIN和,（逗号）在语义上是等价的：两者都在指定的表之间生成笛卡尔积（即，第一个表中的每一行都联接到第二个表中的每一行）。

However, the precedence of the comma operator is less than that of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. 但是，逗号运算符的优先级低于INNER JOIN、CROSS JOIN、LEFT JOIN等运算符的优先级。If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. 如果在存在联接条件时将逗号联接与其他联接类型混合使用，则可能会发生形式为Unknown column 'col_name' in 'on clause'的错误。Information about dealing with this problem is given later in this section.有关处理此问题的信息将在本节后面给出。

The search_condition used with ON is any conditional expression of the form that can be used in a WHERE clause. 与ON一起使用的search_condition是可以在WHERE子句中使用的任何形式的条件表达式。Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set.通常，ON子句用于指定如何联接表的条件，WHERE子句限制要在结果集中包括哪些行。

If there is no matching row for the right table in the ON or USING part in a LEFT JOIN, a row with all columns set to NULL is used for the right table. 如果左联接中的ON或USING部分中没有与右表匹配的行，则右表将使用所有列都设置为NULL的行。You can use this fact to find rows in a table that have no counterpart in another table:您可以使用此事实在表中查找在另一个表中没有对应项的行：

SELECT left_tbl.*
  FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id
  WHERE right_tbl.id IS NULL;

This example finds all rows in left_tbl with an id value that is not present in right_tbl (that is, all rows in left_tbl with no corresponding row in right_tbl). 此示例查找left_tbl中id值不在right_tbl中的所有行（即，left_tbl中的所有行，right_tbl中没有相应的行）。See Section 8.2.1.9, “Outer Join Optimization”.请参阅第8.2.1.9节，“外部连接优化”。

The USING(join_column_list) clause names a list of columns that must exist in both tables. USING(join_column_list)子句指定两个表中必须存在的列的列表。If tables a and b both contain columns c1, c2, and c3, the following join compares corresponding columns from the two tables:如果表a和b都包含列c1、c2和c3，则以下联接将比较这两个表中的相应列：

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

The NATURAL [LEFT] JOIN of two tables is defined to be semantically equivalent to an INNER JOIN or a LEFT JOIN with a USING clause that names all columns that exist in both tables.两个表的NATURAL [LEFT] JOIN定义为在语义上等价于一个INNER JOIN或一个带有USING子句的LEFT JOIN，USING子句命名两个表中存在的所有列。

RIGHT JOIN works analogously to LEFT JOIN. RIGHT JOIN的工作原理类似于LEFT JOIN。To keep code portable across databases, it is recommended that you use LEFT JOIN instead of RIGHT JOIN.为了使代码在数据库间保持可移植性，建议使用LEFT JOIN而不是RIGHT JOIN。

The { OJ ... } syntax shown in the join syntax description exists only for compatibility with ODBC. 联接语法中的那种{ OJ ... }语法描述中显示的语法仅用于与ODBC兼容。The curly braces in the syntax should be written literally; they are not metasyntax as used elsewhere in syntax descriptions.语法中的花括号应该按字面意思写；它们不是语法描述中其他地方使用的元语法。

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;

You can use other types of joins within { OJ ... }, such as INNER JOIN or RIGHT OUTER JOIN. 可以在{ OJ ... }中使用其他类型的联接，例如INNER JOIN或RIGHT OUTER JOIN。This helps with compatibility with some third-party applications, but is not official ODBC syntax.这有助于与某些第三方应用程序兼容，但不是正式的ODBC语法。

STRAIGHT_JOIN is similar to JOIN, except that the left table is always read before the right table. STRAIGHT_JOIN与连接类似，只是左表总是在右表之前读取。This can be used for those (few) cases for which the join optimizer processes the tables in a suboptimal order.这可以用于连接优化器以次优顺序处理表的那些（少数）情况。

Redundant columns of a NATURAL join do not appear. NATURAL联接的冗余列不会出现。Consider this set of statements:考虑这组语句：

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);

In the first SELECT statement, column j appears in both tables and thus becomes a join column, so, according to standard SQL, it should appear only once in the output, not twice. 在第一个SELECT语句中，列j出现在两个表中，因此成为联接列，因此，根据标准SQL，它应该只在输出中出现一次，而不是两次。Similarly, in the second SELECT statement, column j is named in the USING clause and should appear only once in the output, not twice.类似地，在第二个SELECT语句中，列j在USING子句中命名，并且在输出中只出现一次，而不是两次。

Thus, the statements produce this output:因此，语句产生以下输出：

+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+
+------+------+------+
| j    | i    | k    |
+------+------+------+
|    1 |    1 |    1 |
+------+------+------+

Redundant column elimination and column ordering occurs according to standard SQL, producing this display order:根据标准SQL进行冗余列消除和列排序，生成以下显示顺序：

The single result column that replaces two common columns is defined using the coalesce operation. 替换两个公共列的单个结果列是使用合并操作定义的。That is, for two t1.a and t2.a the resulting single join column a is defined as a = COALESCE(t1.a, t2.a), where:也就是说，对于两个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)

If the join operation is any other join, the result columns of the join consist of the concatenation of all columns of the joined tables.如果联接操作是任何其他联接，则联接的结果列由联接表的所有列的串联组成。

A consequence of the definition of coalesced columns is that, for outer joins, the coalesced column contains the value of the non-NULL column if one of the two columns is always NULL. 合并列定义的结果是，对于外部联接，如果两列中的一列始终为NULL，则合并列包含非NULL列的值。If neither or both columns are NULL, both common columns have the same value, so it doesn't matter which one is chosen as the value of the coalesced column. 如果两个列都不为NULL，则两个公共列都具有相同的值，因此选择哪一个作为合并列的值并不重要。A simple way to interpret this is to consider that a coalesced column of an outer join is represented by the common column of the inner table of a JOIN. 解释这一点的一种简单方法是认为外部联接的合并列由JOIN的内部表的公共列表示。Suppose that the tables t1(a, b) and t2(a, c) have the following contents:假设表t1(a,b)和t2(a,c)具有以下内容：

t1    t2
----  ----
1 x   2 z
2 y   3 w

Then, for this join, column a contains the values of t1.a:然后，对于这个连接，列a包含t1.a的值：

mysql> SELECT * FROM t1 NATURAL LEFT JOIN t2;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 | x    | NULL |
|    2 | y    | z    |
+------+------+------+

By contrast, for this join, column a contains the values of t2.a.相反，对于这个连接，列a包含t2.a的值。

mysql> SELECT * FROM t1 NATURAL RIGHT JOIN t2;
+------+------+------+
| a    | c    | b    |
+------+------+------+
|    2 | z    | y    |
|    3 | w    | NULL |
+------+------+------+

Compare those results to the otherwise equivalent queries with JOIN ... ON:将这些结果与具有JJOIN ... 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 clause can be rewritten as an ON clause that compares corresponding columns. USING子句可以重写为比较相应列的ON子句。However, although USING and ON are similar, they are not quite the same. 然而，尽管USING和ON是相似的，但它们并不完全相同。Consider the following two queries:考虑以下两个查询：

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

With respect to determining which rows satisfy the join condition, both joins are semantically identical.关于确定哪些行满足联接条件，两个联接在语义上是相同的。

With respect to determining which columns to display for SELECT * expansion, the two joins are not semantically identical. 关于确定要为SELECT *展开显示哪些列，这两个联接在语义上并不相同。The USING join selects the coalesced value of corresponding columns, whereas the ON join selects all columns from all tables. USING连接选择相应列的合并值，而ON连接选择所有表中的所有列。For the USING join, SELECT * selects these values:对于USING联接，SELECT *选择以下值：

COALESCE(a.c1, b.c1), COALESCE(a.c2, b.c2), COALESCE(a.c3, b.c3)

For the ON join, SELECT * selects these values:对于ON联接，SELECT *选择以下值：

a.c1, a.c2, a.c3, b.c1, b.c2, b.c3

With an inner join, COALESCE(a.c1, b.c1) is the same as either a.c1 or b.c1 because both columns have the same value. 对于内部联接，COALESCE(a.c1,b.c1)与a.c1或b.c1相同，因为两列的值相同。With an outer join (such as LEFT JOIN), one of the two columns can be NULL. 对于外部联接（如LEFT JOIN），两列中的一列可以为NULL。That column is omitted from the result.结果中省略了那一列。

An ON clause can refer only to its operands.ON子句只能引用其操作数。

Example:例如：

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;

The statement fails with an Unknown column 'i3' in 'on clause' error because i3 is a column in t3, which is not an operand of the ON clause. 由于i3是t3中的一列，而t3不是on子句的操作数，因此语句失败，原因是“on子句”错误中出现未知列“i3”。To enable the join to be processed, rewrite the statement as follows:要使联接能够被处理，请按如下所示重写语句：

SELECT * FROM t1 JOIN t2 JOIN t3 ON (i1 = i3);

JOIN has higher precedence than the comma operator (,), so the join expression t1, t2 JOIN t3 is interpreted as (t1, (t2 JOIN t3)), not as ((t1, t2) JOIN t3). JOIN的优先级高于逗号运算符（,），因此JOIN表达式t1, t2 JOIN t3被解释为(t1, (t2 JOIN t3))，而不是((t1, t2) JOIN t3)。This affects statements that use an ON clause because that clause can refer only to columns in the operands of the join, and the precedence affects interpretation of what those operands are.这会影响使用ON子句的语句，因为该子句只能引用联接操作数中的列，并且优先级会影响对这些操作数的解释。

Example:例如：

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);

The JOIN takes precedence over the comma operator, so the operands for the ON clause are t2 and t3. JOIN优先于逗号运算符，因此ON子句的操作数是t2和t3。Because t1.i1 is not a column in either of the operands, the result is an Unknown column 't1.i1' in 'on clause' error.由于t1.i1不是任一操作数中的列，因此结果是“on子句”错误中的未知列“t1.i1”。

To enable the join to be processed, use either of these strategies:要处理联接，请使用以下策略之一：

The same precedence interpretation also applies to statements that mix the comma operator with INNER JOIN, CROSS JOIN, LEFT JOIN, and RIGHT JOIN, all of which have higher precedence than the comma operator.同样的优先级解释也适用于将逗号运算符与INNER JOIN、CROSS JOIN、LEFT JOIN和RIGHT JOIN混合使用的语句，它们的优先级都高于逗号运算符。

A MySQL extension compared to the SQL:2003 standard is that MySQL permits you to qualify the common (coalesced) columns of NATURAL or USING joins, whereas the standard disallows that.与SQL:2003标准相比，MySQL的一个扩展是MySQL允许您限定NATURAL连接或USING连接的公共（合并）列，而标准不允许这样做。