SQL中的连接（JOIN）语句用于将数据库中的两个表或者多个表组合起来。有“连接”生成的集合，可以被保存为表，或者当成表来使用，JOIN语句的含义是把两张表的属性通过它们的值组合在一起。基于ANSI标准的SQL列出五种JOIN方式：

  • 内连接（INNER）
  • 全外连接（FULL OUTER）
  • 左外连接（LEFT OUTER）
  • 右外连接（RIGHT OUTER）
  • 交叉连接（CROSS）

在特定的情况下，一张表（基本表，视图或连接表）可以和自身进行连接，成为自连接（self-join）。 其中， MYSQL不支持全外连接（FULL OUTER）。这里只关注Mysql的剩下的四种连接（JOIN）。

一、示例表

下面有两张表雇员表（Employee）和部门表（Department），ID是主键。

雇员表（Employee）

ID Name DeptID
1 张三 2
2 李四 3
3 王五 3
4 老李 5

部门表（Department）

ID Name
2 研发部
3 测试部
4 运维部

二、内连接（INNER JOIN）

MySQL中，内连接，即等值连接。

1
2

select * from employee, department 
where employee.deptID = department.id;

等效于

1
2
3

select * from 
employee inner join department
on employee.deptID = department.id;

查询结果是：

1
2
3
4
5
6
7
8

+----+--------+--------+----+-----------+
| id | name   | deptID | id | name      |
+----+--------+--------+----+-----------+
|  1 | 张三   |      2 |  2 | 测试部    |
|  2 | 李四   |      3 |  3 | 运维部    |
|  3 | 王五   |      3 |  3 | 运维部    |
+----+--------+--------+----+-----------+
3 rows in set (0.00 sec)

三、左外连接（LEFT JOIN）

Mysql中， left outer join 等效于 left join。左外连接结果中将会保留“左表”中的所有记录，即使右表中没有匹配连接条件，右表中没有匹配条件的列值为NULL。

1

select * from employee left join department on employee.deptId = department.id;

或者

1

select * from employee left outer join department on employee.deptId = department.id;

查询结果：

1
2
3
4
5
6
7
8
9

+----+--------+--------+------+-----------+
| id | name   | deptID | id   | name      |
+----+--------+--------+------+-----------+
|  1 | 张三   |      2 |    2 | 测试部    |
|  2 | 李四   |      3 |    3 | 运维部    |
|  3 | 王五   |      3 |    3 | 运维部    |
|  4 | 老李   |      5 | NULL | NULL      |
+----+--------+--------+------+-----------+
4 rows in set (0.00 sec)

四、右外连接（RIGHT JOIN）

右外连接，它和左外连接完全类似，如果 A 表右连接 B 表, 那么”右表” B 中的每一行在连接表中至少会出现一次. 如果 B 表的记录在”左表” A 中未找到匹配行, 连接表中来源于 A 的列的值设为 NULL。right outer join 等效 right join 。

1

select * from employee right join department on employee.deptId = department.id;

或者

1

select * from employee right outer join department on employee.deptId = department.id;

查询结果：

1
2
3
4
5
6
7
8
9

+------+--------+--------+----+-----------+
| id   | name   | deptID | id | name      |
+------+--------+--------+----+-----------+
|    1 | 张三   |      2 |  2 | 测试部    |
|    2 | 李四   |      3 |  3 | 运维部    |
|    3 | 王五   |      3 |  3 | 运维部    |
| NULL | NULL   |   NULL |  1 | 研发部    |
+------+--------+--------+----+-----------+
4 rows in set (0.00 sec)

五、交叉连接（CROSS JOIN）

交叉连接(cross join)，又称笛卡尔连接(cartesian join)或叉乘(Product)，它是所有类型的内连接的基础。把表视为行记录的集合，交叉连接即返回这两个集合的笛卡尔积。这其实等价于内连接的链接条件为”永真”，或连接条件不存在. 如果 A 和 B 是两个集合，它们的交叉连接就记为: A × B。

1

select * from employee cross join department;

查询结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17

+----+--------+--------+----+-----------+
| id | name   | deptID | id | name      |
+----+--------+--------+----+-----------+
|  1 | 张三   |      2 |  1 | 研发部    |
|  1 | 张三   |      2 |  2 | 测试部    |
|  1 | 张三   |      2 |  3 | 运维部    |
|  2 | 李四   |      3 |  1 | 研发部    |
|  2 | 李四   |      3 |  2 | 测试部    |
|  2 | 李四   |      3 |  3 | 运维部    |
|  3 | 王五   |      3 |  1 | 研发部    |
|  3 | 王五   |      3 |  2 | 测试部    |
|  3 | 王五   |      3 |  3 | 运维部    |
|  4 | 老李   |      5 |  1 | 研发部    |
|  4 | 老李   |      5 |  2 | 测试部    |
|  4 | 老李   |      5 |  3 | 运维部    |
+----+--------+--------+----+-----------+
12 rows in set (0.00 sec)

六、全连接

MYSQL不支持全连接，但可以模拟。全连接是左右外连接的并集，连接表包含左右连接表的所有记录，如果缺少匹配的记录，就以NULL填充。

下面使用left join 和 right join 实现 full join：

1
2
3

select * from employee e left join department d on e.deptId = d.id
union 
select * from  employee e right join department d on e.deptid= d.id

查询结果：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

+------+--------+--------+------+-----------+
| id   | name   | deptID | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      2 |    2 | 测试部    |
|    2 | 李四   |      3 |    3 | 运维部    |
|    3 | 王五   |      3 |    3 | 运维部    |
|    4 | 老李   |      5 | NULL | NULL      |
| NULL | NULL   |   NULL |    1 | 研发部    |
+------+--------+--------+------+-----------+
5 rows in set (0.00 sec)

参考