join 用于多表中字段之间的关联,语法如下:

1

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

JOIN实际上是两个表的的乘积(即笛卡尔积).假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}.

JOIN按照功能大致分为如下三类:

INNER JOIN: 取得两个表中存在连接匹配关系的记录;
LEFT JOIN: 取得左表完全记录，即使右表并无对应匹配记录,如果没有匹配,右侧将包含null;
RIGHT JOIN: 与 LEFT JOIN 相反，取得右表完全记录，即使左表并无匹配对应记录;

. 1.显示join和隐式join

另外,写join语句有所谓的显示join和隐式join的写法:

1
2
3
4
5
6
7
8
9

-- 显示join
select * from
table1 inner join table2
on table1.name = table2.name;

-- 隐式join
select table1.*, table2.*
from table1, table2
where table1.name = table2.name;

这两种写法性能上基本没有差异,参考stackoverflow: http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins

. 2.ON条件和WHERE条件

在执行顺序上需要注意的是on条件和where条件执行顺序: 首先使用on条件产生初始笛卡尔积集合,再在这个集合上使用where条件筛选.所以join的时候应该首先好的on条件保证笛卡尔积集合尽可能小,从而减少Where的执行.

比如下面两种写法,方案2写法保证产生的笛卡尔积集合很小,因而从执行性能来看第二个显然更加省时。

1
2
3
4
5
6
7
8
9
10
11
12

-- 方案1
select * from A
inner join B on B.name = A.name
left join C on C.name = B.name
left join D on D.id = C.id
where C.status>1 and D.status=1;

-- 方案2
select * from A
inner join B on B.name = A.name
left join C on C.name = B.name and C.status>1
left join D on D.id = C.id and D.status=1

. 3.STRAIGHT_JOIN和NATURAL JOIN

. 3.1 STRAIGHT_JOIN

再Join的时候,MySQL优化器要确定以谁为驱动表，也就是说以哪个表为基准，在处理此类问题时，MySQL优化器采用了简单粗暴的解决方法：哪个表的结果集小，就以哪个表为驱动表，当然MySQL优化器实际的处理方式会复杂许多，具体可以参考：http://www.orczhou.com/index.php/2013/04/how-mysql-choose-index-in-a-join/

说明：在EXPLAIN结果中，第一行出现的表就是驱动表。

但是这个由的时候比较愚蠢,比如我们order by的字段在大结果集的表上,也就是说排序字段不在驱动表里，于是乎不可避免的出现了Using filesort和Using temporary.

上面这种场景,我们就可以通过使用STRAIGHT_JOIN显示的指定驱动表

参考: http://huoding.com/2013/06/04/261

. 3.2 NATURAL

MySQL将表中具有相同名称的字段自动进行记录匹配，而这些同名字段类型可以不同。因此，NATURAL JOIN 不用指定匹配条件。

同样包含NATURAL LEFT JOIN和NATURAL RIGHT JOIN.

1

SELECT article.aid,article.title,user.username FROM article NATURAL JOIN user