一．Join语法概述
join 用于多表中字段之间的联系，语法如下：
... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditionatable1:左表；table2:右表。
JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：取得两个表中存在连接匹配关系的记录。
LEFT JOIN（左连接）：取得左表（table1）完全记录，即是右表（table2）并无对应匹配记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，取得右表（table2）完全记录，即是左表（table1）并无匹配对应记录。
注意：mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.
接下来给出一个列子用于解释下面几种分类。用一下两个表做例子：
+----+------------+-----------------+---------+--------+ | id | time | process | name | result | +----+------------+-----------------+---------+--------+ | 1 | 2016-08-31 | wait for result | baidu | 1 | | 2 | 2016-08-25 | haha | tengxun | 1 | | 3 | 2016-08-31 | jiayou | wangyi | 1 | | 9 | 2016-08-31 | happy | wangyi | 1 | +----+------------+-----------------+---------+--------+
+-----+------------+---------+ | id | time | name | +-----+------------+---------+ | 1 | 2016-08-30 | tengxun | | 2 | 2016-09-10 | alibaba | | 8 | NULL | haha | | 9 | 2016-08-31 | wangyi | | 100 | 2014-08-06 | 4399 | +-----+------------+---------+
二.Inner join
内连接，也叫等值连接，inner join产生同时符合A和B的一组数据。
mysql> select * from recuit inner join exam on recuit.id=exam.id; +----+------------+-----------------+---------+--------+----+------------+---------+ | id | time | process | name | result | id | time | name | +----+------------+-----------------+---------+--------+----+------------+---------+ | 1 | 2016-08-31 | wait for result | baidu | 1 | 1 | 2016-08-30 | tengxun | | 2 | 2016-08-25 | haha | tengxun | 1 | 2 | 2016-09-10 | alibaba | | 9 | 2016-08-31 | happy | wangyi | 1 | 9 | 2016-08-31 | wangyi | +----+------------+-----------------+---------+--------+----+------------+---------+ 3 rows in set (0.00 sec)
三.Left join
left join,（或left outer join:在Mysql中两者等价，推荐使用left join.）左连接从左表(A)产生一套完整的记录,与匹配的记录(右表(B)) .如果没有匹配,右侧将包含null。
mysql> select * from recuit left join exam on recuit.id=exam.id; +----+------------+-----------------+---------+--------+------+------------+---------+ | id | time | process | name | result | id | time | name | +----+------------+-----------------+---------+--------+------+------------+---------+ | 1 | 2016-08-31 | wait for result | baidu | 1 | 1 | 2016-08-30 | tengxun | | 2 | 2016-08-25 | haha | tengxun | 1 | 2 | 2016-09-10 | alibaba | | 3 | 2016-08-31 | jiayou | wangyi | 1 | NULL | NULL | NULL | | 9 | 2016-08-31 | happy | wangyi | 1 | 9 | 2016-08-31 | wangyi | +----+------------+-----------------+---------+--------+------+------------+---------+ 4 rows in set (0.00 sec)
四.Right join
mysql> select * from recuit right join exam on recuit.id=exam.id; +------+------------+-----------------+---------+--------+-----+------------+---------+ | id | time | process | name | result | id | time | name | +------+------------+-----------------+---------+--------+-----+------------+---------+ | 1 | 2016-08-31 | wait for result | baidu | 1 | 1 | 2016-08-30 | tengxun | | 2 | 2016-08-25 | haha | tengxun | 1 | 2 | 2016-09-10 | alibaba | | 9 | 2016-08-31 | happy | wangyi | 1 | 9 | 2016-08-31 | wangyi | | NULL | NULL | NULL | NULL | NULL | 8 | NULL | haha | | NULL | NULL | NULL | NULL | NULL | 100 | 2014-08-06 | 4399 | +------+------------+-----------------+---------+--------+-----+------------+---------+ 5 rows in set (0.00 sec)
同left join，只不过是保留了表B的项目，表A中没有相对应的项被置为NULL。
五.Cross join
cross join：交叉连接，得到的结果是两个表的乘积，即笛卡尔积
笛卡尔（Descartes）乘积又叫直积。假设集合A={a,b}，集合B={0,1,2}，则两个集合的笛卡尔积为{(a,0),(a,1), (a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有，如果A表示某学校学生的集合，B表示该学校所有课程的集合，则A与B的笛卡尔积表示所有可能的选 课情况。
mysql> select * from recuit cross join exam; +----+------------+-----------------+---------+--------+-----+------------+---------+ | id | time | process | name | result | id | time | name | +----+------------+-----------------+---------+--------+-----+------------+---------+ | 1 | 2016-08-31 | wait for result | baidu | 1 | 1 | 2016-08-30 | tengxun | | 2 | 2016-08-25 | haha | tengxun | 1 | 1 | 2016-08-30 | tengxun | | 3 | 2016-08-31 | jiayou | wangyi | 1 | 1 | 2016-08-30 | tengxun | | 9 | 2016-08-31 | happy | wangyi | 1 | 1 | 2016-08-30 | tengxun | | 1 | 2016-08-31 | wait for result | baidu | 1 | 2 | 2016-09-10 | alibaba | | 2 | 2016-08-25 | haha | tengxun | 1 | 2 | 2016-09-10 | alibaba | | 3 | 2016-08-31 | jiayou | wangyi | 1 | 2 | 2016-09-10 | alibaba | | 9 | 2016-08-31 | happy | wangyi | 1 | 2 | 2016-09-10 | alibaba | | 1 | 2016-08-31 | wait for result | baidu | 1 | 8 | NULL | haha | | 2 | 2016-08-25 | haha | tengxun | 1 | 8 | NULL | haha | | 3 | 2016-08-31 | jiayou | wangyi | 1 | 8 | NULL | haha | | 9 | 2016-08-31 | happy | wangyi | 1 | 8 | NULL | haha | | 1 | 2016-08-31 | wait for result | baidu | 1 | 9 | 2016-08-31 | wangyi | | 2 | 2016-08-25 | haha | tengxun | 1 | 9 | 2016-08-31 | wangyi | | 3 | 2016-08-31 | jiayou | wangyi | 1 | 9 | 2016-08-31 | wangyi | | 9 | 2016-08-31 | happy | wangyi | 1 | 9 | 2016-08-31 | wangyi | | 1 | 2016-08-31 | wait for result | baidu | 1 | 100 | 2014-08-06 | 4399 | | 2 | 2016-08-25 | haha | tengxun | 1 | 100 | 2014-08-06 | 4399 | | 3 | 2016-08-31 | jiayou | wangyi | 1 | 100 | 2014-08-06 | 4399 | | 9 | 2016-08-31 | happy | wangyi | 1 | 100 | 2014-08-06 | 4399 | +----+------------+-----------------+---------+--------+-----+------------+---------+ 20 rows in set (0.00 sec)
六.Full join
mysql不支持full join，可以用right join和left join模拟
mysql> select * from exam left join recuit on exam.id=recuit.id
-> union
-> select * from exam right join recuit on exam.id = recuit.id;
+------+------------+---------+------+------------+-----------------+---------+--------+
| id | time | name | id | time | process | name | result |
+------+------------+---------+------+------------+-----------------+---------+--------+
| 1 | 2016-08-30 | tengxun | 1 | 2016-08-31 | wait for result | baidu | 1 |
| 2 | 2016-09-10 | alibaba | 2 | 2016-08-25 | haha | tengxun | 1 |
| 9 | 2016-08-31 | wangyi | 9 | 2016-08-31 | happy | wangyi | 1 |
| 8 | NULL | haha | NULL | NULL | NULL | NULL | NULL |
| 100 | 2014-08-06 | 4399 | NULL | NULL | NULL | NULL | NULL |
| NULL | NULL | NULL | 3 | 2016-08-31 | jiayou | wangyi | 1 |
+------+------------+---------+------+------------+-----------------+---------+--------+
6 rows in set (0.25 sec)