参见MySQL(以5.1为例)中官方手册：
假设有以下几个表
| id | book |
|---|---|
| 1 | java |
| 2 | c++ |
| 3 | php |
| id | author |
|---|---|
| 2 | zhang |
| 3 | wang |
| 4 | li |
| author | year |
|---|---|
| zhang | 2003 |
| ma | 2006 |
| liu | 2011 |
Inner Join 内连接
将两个表中存在连接关系的字段，组成的记录集，叫做内连接。内连接等价于mysql> select t1.id as id,book,author from t1, t2 where t1.id=t2.id;+------+------+--------+| id | book | author |+------+------+--------+| 2 | c++ | zhang || 3 | php | wang |+------+------+--------+2 rows in set (0.00 sec)mysql> select * from t1 inner join t2 using (id);+------+------+--------+| id | book | author |+------+------+--------+| 2 | c++ | zhang || 3 | php | wang |+------+------+--------+2 rows in set (0.00 sec)
可以看出，两者是等价的。没有Using子句的Inner Join相当于是求两个表的笛卡尔积。
Cross Join 交叉连接在Mysql中，Cross Join可以用逗号表达式表示，例如(t1, t2)。在Mysql中，Cross Join 和 Inner Join 是等价的，但是在标准SQL中，它们并不等价，Inner Join 用于带有on表达式的连接，反之用Cross Join。以下两个SQL语句是等价的。Cross Join 指的是两个table的笛卡尔积。以下三句SQL是等价的。mysql> select * from t1 inner join t2;mysql> select * from t1 cross join t2;mysql> select * from (t1, t2);mysql> select * from t1 nature join t2;结果集：+------+------+------+--------+| id | book | id | author |+------+------+------+--------+| 1 | java | 2 | zhang || 2 | c++ | 2 | zhang || 3 | php | 2 | zhang || 1 | java | 3 | wang || 2 | c++ | 3 | wang || 3 | php | 3 | wang || 1 | java | 4 | li || 2 | c++ | 4 | li || 3 | php | 4 | li |+------+------+------+--------+
下面两句SQL也是等价的。
mysql> select * from table1 left join (table2, table3) on (table2.id = table1.id and table2.author = table3.author);mysql> select * from table1 left join (table2 cross join table3) on (table2.id = table1.id and table2.author = table3.author);结果集：+------+------+------+--------+--------+------+| id | book | id | author | author | year |+------+------+------+--------+--------+------+| 1 | java | NULL | NULL | NULL | NULL || 2 | c++ | 2 | zhang | zhang | 2003 || 3 | php | NULL | NULL | NULL | NULL |+------+------+------+--------+--------+------+
Natural Join 自然连接
NATURAL [LEFT] JOIN：这个句子的作用相当于INNER JOIN，或者是在USING子句中包含了联结的表中所有公共字段的Left JOIN（左联结）。也就是说：下面两个SQL是等价的。mysql> select * from table1 natural join table2;mysql> select * from table1 inner join table2 using (id);结果集：+------+------+--------+| id | book | author |+------+------+--------+| 2 | c++ | zhang || 3 | php | wang |+------+------+--------+
同时，下面两个SQL也是等价的。
mysql> select * from table1 natural left join table2;mysql> select * from table1 left join table2 using(id);结果集：+------+------+--------+| id | book | author |+------+------+--------+| 1 | java | NULL || 2 | c++ | zhang || 3 | php | wang |+------+------+--------+
Left Join 左外连接
左外连接A、B表的意思就是将表A中的全部记录和表B中字段连接形成的记录集，这里注意的是最后出来的记录集会包括表A的全部记录。左连接表1，表二等价于右连接表二，表一。如下两个SQL是等价的：mysql> select * from table1 left join table2 using (id);mysql> select * from table2 right join table1 using (id);结果集：+------+------+--------+| id | book | author |+------+------+--------+| 1 | java | NULL || 2 | c++ | zhang || 3 | php | wang |+------+------+--------+
Right Join 右外连接
右外连接和左外连接是类似的。为了方便数据库便于访问，推荐使用左外连接代替右外连接。
Mysql表连接的一些注意事项：
1、两个表求差集的方法如果求 左表 - 右表 的差集，使用类似下面的SQL：SELECT left_tbl.* FROM left_tbl LEFT JOIN right_tbl ON left_tbl.id = right_tbl.id WHERE right_tbl.id IS NULL;例如mysql> select table1.* from table1 left join table2 using(id) where table2.id is null;+------+------+| id | book |+------+------+| 1 | java |+------+------+1 row in set (0.00 sec)
2、Using子句
Using子句可以使用On子句重写。但是使用Select * 查询出的结果有差别。以下两句话是等价的：mysql> select id, book, author from table1 join table2 using (id);mysql> select table1.id, book, author from table1 join table2 on table1.id=table2.id;结果集：+------+------+--------+| id | book | author |+------+------+--------+| 2 | c++ | zhang || 3 | php | wang |+------+------+--------+
但是下面两个有些许不同，使用on时候，重复的部分会被输出两次。
mysql> select * from table1 join table2 using (id);+------+------+--------+| id | book | author |+------+------+--------+| 2 | c++ | zhang || 3 | php | wang |+------+------+--------+2 rows in set (0.00 sec)mysql> select * from table1 join table2 on table1.id=table2.id;+------+------+------+--------+| id | book | id | author |+------+------+------+--------+| 2 | c++ | 2 | zhang || 3 | php | 3 | wang |+------+------+------+--------+2 rows in set (0.00 sec)
3、Straight Join的使用
STRAIGHT_JOIN 和 JOIN相似，除了大部分情况下，在使用STRAIGHT_JOIN时候，先读右表后读左表。而在大部分情况下是先读左表的。STRAIGHT_JOIN仅用于少数情况下的表连接性能优化，比如右表记录数目明显少于左表。4、Mysql表连接的运算顺序在MySQL 5.1版本中，INNER JOIN, CROSS JOIN, LEFT JOIN, 和RIGHT JOIN 比逗号表达式具有更高的优先级。因此SQL1被解析成SQL3，而不是SQL2。SQL1 : SELECT * FROM t1, t2 JOIN t3 ON (t1.i1 = t3.i3);SQL2 : SELECT * FROM (t1, t2) JOIN t3 ON (t1.i1 = t3.i3);SQL3 : SELECT * FROM t1, (t2 JOIN t3 ON (t1.i1 = t3.i3));
因此会报错，找不到i1列。因此以后在写这样的查询的时候，最好写明白，不要省略括号，这样能避免很多错误。
5、循环的自然连接在MySQL 5.1版本中，SQL1等价于SQL3， 而在MySQL以前版本中，SQL1等价于SQL2。SQL1 : SELECT ... FROM t1 NATURAL JOIN t2 NATURAL JOIN t3; SQL2 : SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c; SQL3 : SELECT ... FROM t1, t2, t3 WHERE t1.b = t2.b AND t2.c = t3.c AND t1.a = t3.a;