FROM或 WHERE 子句中指定，WHERE 和 HAVING 子句可包含其搜索条件，以供进一步筛选连接之后的结果集。 目前可实现的连接有：自然连接(Natural Join)，内连接(Inner Join), 外连接(Outer Join), 交叉连接(Cross Join) etc.JOIN USING 可按照指定的列实现表的等值连接。设有两个表t1, t2 具有相同的列 a, b, c, d, 如果不是对全部相同列做连接，而是是对列 a, b 做连接，可写成
t1 JOIN t2 USING(a,b)。JOIN ON 可按照更一般性条件实现表的等值连接。eg: t1(a, b), t2(a, c), 可写成
t1 JOIN t2 ON t1.a = t2.a。USING 后用于连接的列，也可用保留字 ON 指定， eg:
... USIGN(a)... 等价于 ... ON t1.a = t2.a..., 使用两者在结果集上体现的不同请见注 1。示例表 t1, t2, 其数据如下：
[sql]
mysql> SELECT * FROM t1;
+---+------+
| a | b |
+---+------+
| 1 | a |
| 3 | c |
| 5 | e |
| 7 | g |
+---+------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM t2;
+---+------+
| a | c |
+---+------+
| 2 | b |
| 4 | d |
| 6 | f |
| 7 | g |
+---+------+
4 rows in set (0.00 sec)
[/sql]
自然连接
[sql]
mysql> SELECT * FROM t1, t2 WHERE t1.a = t2.a;
+---+------+---+------+
| a | b | a | c |
+---+------+---+------+
| 7 | g | 7 | g |
+---+------+---+------+
1 row in set (0.00 sec)
[/sql]
内连接
[sql]
mysql> SELECT * FROM t1 INNER JOIN t2 USING (a);
+---+------+------+
| a | b | c |
+---+------+------+
| 7 | g | g |
+---+------+------+
1 row in set (0.00 sec)
[/sql]
它等价于：
SELECT * FROM t1, t2 WHERE t1.a = t2.a; or SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a;。外连接
外连接是连接的扩展。一般连接操作的结果表由符合连接条件的匹配元组连接起来的新元组构成，其余不符合连接条件的非匹配元组则被丢弃。外连接允许在结果表中保留非匹配元组，空缺部分填以
NULL。其作用是在做连接操作时避免丢失信息。外连接有 3 类：1 左外连接(Left Outer Join)。连接运算谓词为
LEFT [OUTER] JOIN，其结果表中保留左关系的所有元组。eg: [sql]
mysql> SELECT * FROM t1 LEFT JOIN t2 USING(a);
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | a | NULL |
| 3 | c | NULL |
| 5 | e | NULL |
| 7 | g | g |
+---+------+------+
4 rows in set (0.00 sec)
[/sql]
等价于：
SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;。2 右外连接(Right Outer Join)。连接运算谓词为
RIGHT [OUTER] JOIN, 其结果表中保留右关系的所有元组。MySQL 对其的优化策略见注 2。3 全外连接(Full Outer Join)。连接运算谓词为
FULL [OUTER] JOIN, 其结果表中保留左右关系的所有元组。 MySQL 中， FULL JOIN... USING的结果集和INNER JOIN ... USING的结果集相同，且 MySQL 不支持 FULL OUTER JOIN。eg: [sql]
mysql> SELECT * FROM t1 FULL JOIN t2 USING(a);
+---+------+------+
| a | b | c |
+---+------+------+
| 7 | g | g |
+---+------+------+
1 row in set (0.00 sec)
mysql> SELECT * FROM t1 FULL OUTER JOIN t2 USING(a);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OUTER JOIN t2 USING(a)' at line 1
[/sql]
交叉连接
交叉连接等同于做笛卡尔积。
[sql]
mysql> SELECT * FROM t1 CROSS JOIN t2;
+---+------+---+------+
| a | b | a | c |
+---+------+---+------+
| 1 | a | 2 | b |
| 3 | c | 2 | b |
| 5 | e | 2 | b |
| 7 | g | 2 | b |
| 1 | a | 4 | d |
| 3 | c | 4 | d |
| 5 | e | 4 | d |
| 7 | g | 4 | d |
| 1 | a | 6 | f |
| 3 | c | 6 | f |
| 5 | e | 6 | f |
| 7 | g | 6 | f |
| 1 | a | 7 | g |
| 3 | c | 7 | g |
| 5 | e | 7 | g |
| 7 | g | 7 | g |
+---+------+---+------+
16 rows in set (0.00 sec)
[/sql]
它等价于：
SELECT * FROM t1, t2; or SELECT * FROM t1 INNER JOIN t2;。注：
- USING 和 ON 显示的结果说明
为了使 MySQL 在解析NATURAL JOIN和JOIN ... USINGSQL 时采用 SQL 2003 的标准，从 MySQL 5.0.12 开始，这两种 SQL 的解析都有做调整,NATURAL JOIN时， 查询结果中只会显示单独的唯一一列， 即 t1.a, t2.2 两列做自然连接后在结果中只有一列 a (= COELSCE(t1.a, t2.a)); 而JOIN ... USING时，对 USING 中 指定使用做连接的列，查询结果中也只会显示单独的唯一一列，JOIN ... USING形式的变体如LEFT JOIN ... USING,RIGHT JOIN ... USING等解析优化也是采用同样的处理。除此之后，其他的连接方式还是遵照以前的规则。详细说明，可查看如下引用部分或MySQL 官方文档 JOIN Syntax一节。
Join Processing Changes in MySQL 5.0.12
Beginning with MySQL 5.0.12, natural joins and joins with USING, including outer join variants, are processed according to the SQL:2003 standard. The goal was to align the syntax and semantics of MySQL with respect to NATURAL JOIN and JOIN ... USING according to SQL:2003. However, these changes in join processing can result in different output columns for some joins. Also, some queries that appeared to work correctly in older versions must be rewritten to comply with the standard.
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:
COALESCE(x, y) = (CASE WHEN V1 IS NOT NULL THEN V1 ELSE V2 END)
If the join operation is any other join, the result columns of the join consists of the concatenation of all columns of the joined tables. This is the same as previously.
[sql]
mysql> SELECT * FROM t1 LEFT JOIN t2 USING (a);
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | a | NULL |
| 3 | c | NULL |
| 5 | e | NULL |
| 7 | g | g |
+---+------+------+
4 rows in set (0.01 sec)
mysql> SELECT * FROM t1 LEFT JOIN t2 ON t1.a = t2.a;
+---+------+------+------+
| a | b | a | c |
+---+------+------+------+
| 1 | a | NULL | NULL |
| 3 | c | NULL | NULL |
| 5 | e | NULL | NULL |
| 7 | g | 7 | g |
+---+------+------+------+
4 rows in set (0.01 sec)
[/sql] - 2右外连接的优化策略
右外连接 SQL 在解析阶段都会转换为只包含左外连接的 SQL，一般遵循如下这样的转换方式：
T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =
(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)
有关其详细信息，请参考 MySQL 的官方文档7.3.1.10. Outer Join Simplification。
At the parser stage, queries with right outer joins operations are converted to equivalent queries containing only left join operations. In the general case, the conversion is performed according to the following rule:
T1, ...) RIGHT JOIN (T2,...) ON P(T1,...,T2,...) =
(T2, ...) LEFT JOIN (T1,...) ON P(T1,...,T2,...)
通过EXPLAIN EXTENDED,SHOW WARNINGS, 查看 MySQL 解析优化后的右外连接：
[sql]
mysql> EXPLAIN EXTENDED SELECT * FROM t1 RIGHT JOIN t2 USING (a);
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------+
| 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | |
| 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.a | 1 | 100.00 | |
+----+-------------+-------+--------+---------------+---------+---------+-----------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | select `test`.`t2`.`a` AS `a`,`test`.`t2`.`c` AS `c`,`test`.`t1`.`b` AS `b` from `test`.`t2` left join `test`.`t1` on((`test`.`t1`.`a` = `test`.`t2`.`a`)) where 1 |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
[/sql]
