SQL连接操作

一．Join语法概述

join 用于多表中字段之间的联系，语法如下：

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

table1:左表；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)