写在前面

迄今为止我们都是在一张表内进行数据的增删改查操作，但是在实际工作中大部分都是涉及到同时操作多个表的情况，因此就需要使用到本篇学习的连接查询。

本文的主要内容如下：（1）笛卡尔乘积；（2）SQL中笛卡尔乘积语法；（3）内连接；（4）内连接—无连接条件；（5）内连接—有连接条件；（6）内连接—组合条件；（7）自连接；（8）外连接；（9）左连接（左外连接）；（10）右连接（右外连接）；（11）；（12）全外连接（MySQL没有）；（13）文章总结。

前期数据

为了更好的学习连接查询，这里提前创建两张表和插入一些数据。

第一步，创建两张表：部门表(t_department)和员工表(t_employee)，其中员工表内部有个department_id引用来自部门表的id，相应的SQL语句如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> drop table if exists t_department;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t_department(
    -> id int not null auto_increment primary key comment '部门id',
    -> department_name varchar(32) not null default '' comment '部门名称'
    -> )comment '部门表';
Query OK, 0 rows affected (0.03 sec)

mysql> drop table if exists t_employee;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t_employee(
    -> id int not null auto_increment primary key comment '员工id',
    -> employee_name varchar(32) not null default '' comment '员工姓名',
    -> department_id int not null default 0 comment '员工所在部门id'
    -> )comment '员工表';
Query OK, 0 rows affected (0.02 sec)

第二步，插入数据，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
mysql> insert into t_department values(1,'技术部'),(2,'财务部'),(3,'人事部'),(4,'采购部');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> insert into t_employee values(1,'aa',1),(2,'bb',2),(3,'cc',3),(4,'dd',0),(5,'ee',0);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from t_department;
+----+-----------------+
| id | department_name |
+----+-----------------+
|  1 | 技术部          |
|  2 | 财务部          |
|  3 | 人事部          |
|  4 | 采购部          |
+----+-----------------+
4 rows in set (0.00 sec)

mysql> select * from t_employee;
+----+---------------+---------------+
| id | employee_name | department_id |
+----+---------------+---------------+
|  1 | aa            |             1 |
|  2 | bb            |             2 |
|  3 | cc            |             3 |
|  4 | dd            |             0 |
|  5 | ee            |             0 |
+----+---------------+---------------+
5 rows in set (0.00 sec)

笛卡尔乘积

笛卡尔乘积是指集合A中元素和集合B中元素任意相互关联产生的所有可能的结果。

假设集合A中有m个元素，集合B中有n和元素，那么集合A和B进行笛卡尔乘积会产生m*n个结果，其实就相当于循环遍历两个集合中的元素，然后任意组合而成。

使用Java编写的伪代码如下所示：

1
2
3
4
5
for(Object elementA : A){
  for(Object elementB : B){
    System.out.print(elementA+","+elementB);
  }
}

其实就是先将A集合中的第一行去匹配B集合中的所有行，然后A集合中的第二行去匹配B集合中的所有行，依次类推，最后结果数为m*n。

SQL中笛卡尔乘积语法

接下来学习SQL中笛卡尔乘积的表示语法，如下所示：

1
2
3
select 字段 from 表1,表2,[...表N];
或者
select 字段 from 表1 join 表2,...[join 表N];

例子1：对部门表和员工表进行笛卡尔乘积运算：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> select * from t_department,t_employee;
+----+-----------------+----+---------------+---------------+
| id | department_name | id | employee_name | department_id |
+----+-----------------+----+---------------+---------------+
|  1 | 技术部          |  1 | aa            |             1 |
|  2 | 财务部          |  1 | aa            |             1 |
|  3 | 人事部          |  1 | aa            |             1 |
|  4 | 采购部          |  1 | aa            |             1 |
|  1 | 技术部          |  2 | bb            |             2 |
|  2 | 财务部          |  2 | bb            |             2 |
|  3 | 人事部          |  2 | bb            |             2 |
|  4 | 采购部          |  2 | bb            |             2 |
|  1 | 技术部          |  3 | cc            |             3 |
|  2 | 财务部          |  3 | cc            |             3 |
|  3 | 人事部          |  3 | cc            |             3 |
|  4 | 采购部          |  3 | cc            |             3 |
|  1 | 技术部          |  4 | dd            |             0 |
|  2 | 财务部          |  4 | dd            |             0 |
|  3 | 人事部          |  4 | dd            |             0 |
|  4 | 采购部          |  4 | dd            |             0 |
|  1 | 技术部          |  5 | ee            |             0 |
|  2 | 财务部          |  5 | ee            |             0 |
|  3 | 人事部          |  5 | ee            |             0 |
|  4 | 采购部          |  5 | ee            |             0 |
+----+-----------------+----+---------------+---------------+
20 rows in set (0.00 sec)

我们知道t_department表有4条记录，t_employee表有5条记录，那么笛卡尔乘积之后就有了20条记录。

内连接

内连接其实就当于在笛卡尔乘积的基础上添加了连接条件，也就是说当内连接没有连接条件的时候，此时就是笛卡尔乘积。

这里和单表查询最大的区别在于select后面指定的列分别属于两个不同的表，始终记住select后面跟的是需要显示的信息，from是数据来源，即使用到的数据表，而where后面则是过滤条件，最后返回的就是由这两个表中不同列值组成的新记录。

内连接对应的语法如下所示：

1
2
3
4
5
select 字段 from 表1 inner join 表2 on 连接条件;
或者
select 字段 from 表1 join 表2 on 连接条件;
或者
select 字段 from 表1,表2 [where 关联条件];

使用Java编写的伪代码如下所示：

1
2
3
4
5
6
7
for(Object elementA : A){
  for(Object elementB : B){
    if(连接条件是否为true){
      System.out.print(elementA+","+elementB);
    }
  }
}

无连接条件

前面也说了，当内连接没有连接条件的时候，此时就是笛卡尔乘积。

例子2：内连接没有连接条件的时候，此时就是笛卡尔乘积：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
mysql> select e.employee_name,d.department_name from t_employee as e inner join t_department as d;
+---------------+-----------------+
| employee_name | department_name |
+---------------+-----------------+
| aa            | 技术部          |
| aa            | 财务部          |
| aa            | 人事部          |
| aa            | 采购部          |
| bb            | 技术部          |
| bb            | 财务部          |
| bb            | 人事部          |
| bb            | 采购部          |
| cc            | 技术部          |
| cc            | 财务部          |
| cc            | 人事部          |
| cc            | 采购部          |
| dd            | 技术部          |
| dd            | 财务部          |
| dd            | 人事部          |
| dd            | 采购部          |
| ee            | 技术部          |
| ee            | 财务部          |
| ee            | 人事部          |
| ee            | 采购部          |
+---------------+-----------------+
20 rows in set (0.00 sec)

有连接条件

例子3：内连接存在连接条件的时候，此时就是所有查询出的结果都是能够在连接的表中有对应记录：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
mysql> select e.employee_name,d.department_name from t_employee as e inner join t_department as d on e.department_id = d.id;
+---------------+-----------------+
| employee_name | department_name |
+---------------+-----------------+
| aa            | 技术部          |
| bb            | 财务部          |
| cc            | 人事部          |
+---------------+-----------------+
3 rows in set (0.00 sec)

mysql> select e.employee_name,d.department_name from t_employee as e join t_department as d on e.department_id = d.id;
+---------------+-----------------+
| employee_name | department_name |
+---------------+-----------------+
| aa            | 技术部          |
| bb            | 财务部          |
| cc            | 人事部          |
+---------------+-----------------+
3 rows in set (0.00 sec)

mysql> select e.employee_name,d.department_name from t_employee as e,t_department as d where e.department_id = d.id;
+---------------+-----------------+
| employee_name | department_name |
+---------------+-----------------+
| aa            | 技术部          |
| bb            | 财务部          |
| cc            | 人事部          |
+---------------+-----------------+
3 rows in set (0.00 sec)

可以看到这里使用了前面介绍的三种语句，都查询出两个表的交集记录：

组合条件

例子4：内连接存在连接条件，且存在除连接条件以外的查询条件。

需求是查询技术部的员工信息：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select e.employee_name,d.department_name from t_employee as e inner join t_department as d on e.department_id = d.id and d.department_name = '技术部';
+---------------+-----------------+
| employee_name | department_name |
+---------------+-----------------+
| aa            | 技术部          |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> select e.employee_name,d.department_name from t_employee as e join t_department as d on e.department_id = d.id where d.department_name = '技术部';
+---------------+-----------------+
| employee_name | department_name |
+---------------+-----------------+
| aa            | 技术部          |
+---------------+-----------------+
1 row in set (0.00 sec)

mysql> select e.employee_name,d.department_name from t_employee as e,t_department as d where e.department_id = d.id and d.department_name = '技术部';
+---------------+-----------------+
| employee_name | department_name |
+---------------+-----------------+
| aa            | 技术部          |
+---------------+-----------------+
1 row in set (0.00 sec)

可以看到，这里同样使用了三种方式，其中第一种在on中使用了and形成组合条件查询；第二种是在连接的结果之后进行过滤，其实就相当于先获取连接的结果，然后再使用where中的条件来对连接结果进行过滤；第三种则是直接在where条件后面进行过滤。

很明显第三种方式逻辑较为清晰，使用where子句时的写法比较简单，但是inner join语法是ANSI SQL的标准规范，且使用inner join连接查询时，能够确保不会忘记连接条件，而且在某些情况下使用where关键字会影响到查询的性能。

自连接

前面我们提到过自连接查询，也就是如果在一个连接查询中，涉及的两个表都是同一个表，那么这种查询称为自连接查询。自连接查询时一种特殊的内连接查询，它是指相互连接的表在物理上为同一张表，但是在逻辑上可以分为两张表。

接下来为了演示自连接查询，需要在t_employee表中新增2条记录，如下所示：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
mysql> insert into t_employee values(6,'ff',1),(7,'gg',1);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_employee;
+----+---------------+---------------+
| id | employee_name | department_id |
+----+---------------+---------------+
|  1 | aa            |             1 |
|  2 | bb            |             2 |
|  3 | cc            |             3 |
|  4 | dd            |             0 |
|  5 | ee            |             0 |
|  6 | ff            |             1 |
|  7 | gg            |             1 |
+----+---------------+---------------+
7 rows in set (0.00 sec)

例子5：下面的例子展示了，查询employee_name为aa的员工所在部门的全部员工信息，并显示employee_id和employee_name：

1
2
3
4
5
6
7
8
9
mysql> select t1.id,t1.employee_name from t_employee as t1, t_employee as t2 where t1.department_id=t2.department_id and t2.employee_name ='aa';
+----+---------------+
| id | employee_name |
+----+---------------+
|  1 | aa            |
|  6 | ff            |
|  7 | gg            |
+----+---------------+
3 rows in set (0.00 sec)

这里查询的两个表是相同的表，为了防止产生二义性，对表使用了别名，t_employee表第一次出现的别名为t1，第二次出现的别名为t2，使用select语句返回列时明确指出返回以t1为前缀的列的全名，where连接两个表，并按照第二个表的department_id对数据进行过滤，进而返回所需数据。

也就是说，自连接查询一般用于表中的某个字段的值引用于另一个字段的值，如权限表中的父权限也属于权限等。

外连接

外连接涉及到两个表，分别是主表和从表，注意当需要被查询的信息主要来自哪个表，那么那个表就是主表。

外连接查询的结果是主表中的所有记录。但是如果从表存在与之匹配的记录，则显示匹配的值，这部分就相当于内连接查询出来的结果；如果从表中不存在与之匹配的记录，则显示null。

也就是说，最终的返回结果(即外连接查询结果 )= 内连接结果 + 主表中有的而内连接结果中没有的记录。

因此，外连接分为两种情况：左外连接和右外连接。

左外连接，使用left outer join关键字，简写left join，需要注意的是left join左边的是主表：

右外连接，使用right outer join关键字，简写right join，需要注意的是right join右边的是主表：

左连接（左外连接）

左连接（左外连接）的语法格式如下所示：

1
select 列 from 主表 left join 从表 on 连接条件;

请注意，无论是左连接还是右连接，on后面紧跟的都是主表中的数据。

例子6：下面的例子展示了，查询所有员工信息及其所在部门，显示员工名称和部门名称：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select e.employee_name,d.department_name from t_employee as e left join t_department as d on e.department_id = d.id;
+---------------+-----------------+
| employee_name | department_name |
+---------------+-----------------+
| aa            | 技术部          |
| ff            | 技术部          |
| gg            | 技术部          |
| bb            | 财务部          |
| cc            | 人事部          |
| dd            | NULL            |
| ee            | NULL            |
+---------------+-----------------+
7 rows in set (0.00 sec)

可以看到这查询到了所有的员工信息及其部门信息，且department_id为0时，其department_name的值为NULL。

例子7：下面的例子展示了，查询所有归属部门的员工信息及其所在部门（也就是department_id不为0的记录），显示员工名称和部门名称：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql> select e.employee_name,d.department_name from t_employee as e left join t_department as d on e.department_id = d.id where e.department_id !=0;
+---------------+-----------------+
| employee_name | department_name |
+---------------+-----------------+
| aa            | 技术部          |
| ff            | 技术部          |
| gg            | 技术部          |
| bb            | 财务部          |
| cc            | 人事部          |
+---------------+-----------------+
5 rows in set (0.00 sec)

mysql> select e.employee_name,d.department_name from t_employee as e left join t_department as d on e.department_id = d.id where d.department_name is not null;
+---------------+-----------------+
| employee_name | department_name |
+---------------+-----------------+
| aa            | 技术部          |
| bb            | 财务部          |
| cc            | 人事部          |
| ff            | 技术部          |
| gg            | 技术部          |
+---------------+-----------------+
5 rows in set (0.00 sec)

其实这也就是在外连接的基础上，再使用where条件语句对结果进行过滤而已。

右连接（右外连接）

右连接（右外连接）的语法格式如下所示：

1
select 列 from 从表 right join 主表 on 连接条件;

请注意，无论是左连接还是右连接，on后面紧跟的都是主表中的数据。

例子8：实现和 例子6 一样的需求，即查询所有员工信息及其所在部门，显示员工名称和部门名称：

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> select d.department_name,e.employee_name from t_department as d right join t_employee as e on e.department_id = d.id;
+-----------------+---------------+
| department_name | employee_name |
+-----------------+---------------+
| 技术部          | aa            |
| 技术部          | ff            |
| 技术部          | gg            |
| 财务部          | bb            |
| 人事部          | cc            |
| NULL            | dd            |
| NULL            | ee            |
+-----------------+---------------+
7 rows in set (0.00 sec)

例子9：实现和 例子7 一样的需求，即查询所有归属部门的员工信息及其所在部门（也就是department_id不为0的记录），显示员工名称和部门名称：

1
2
3
4
5
6
7
8
9
10
11
mysql> select d.department_name,e.employee_name from t_department as d right join t_employee as e on e.department_id = d.id where e.department_id !=0;
+-----------------+---------------+
| department_name | employee_name |
+-----------------+---------------+
| 技术部          | aa            |
| 技术部          | ff            |
| 技术部          | gg            |
| 财务部          | bb            |
| 人事部          | cc            |
+-----------------+---------------+
5 rows in set (0.00 sec)

复合条件连接

复合条件查询就是在连接查询的过程中，通过添加过滤条件，限制查询的结果，使查询的结果更为准确。

例子10： 实现和 例子7 一样的需求，SQL也不用变化，这里主要介绍复合条件查询这个概念而已，在实际工作中其实分的不是很明确。

全连接（MySQL没有）

全连接(全外连接)就是将两张表的字段都查出来，某个表中没有对应的值就显示null。但是请注意，MySQL中不存在全外连接的，即不存在full outer join关键字：

如果开发者想要实现全外连接的功能，可以使用union关键字来连接左连接和右连接。

例子11：下面的例子，查询所有员工信息和部门信息（包括department_id为0的记录），显示员工名称和部门名称：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select d.department_name,e.employee_name from t_department as d left join t_employee as e on e.department_id = d.id
    -> union
    -> select d.department_name,e.employee_name from t_department as d right join t_employee as e on e.department_id = d.id;
+-----------------+---------------+
| department_name | employee_name |
+-----------------+---------------+
| 技术部          | aa            |
| 技术部          | ff            |
| 技术部          | gg            |
| 财务部          | bb            |
| 人事部          | cc            |
| NULL            | dd            |
| NULL            | ee            |
| 采购部          | NULL          |
+-----------------+---------------+
8 rows in set (0.00 sec)

如果开发者使用的是oracle，那么就可以直接使用full outer join关键字来进行全连接查询。

文章总结

最后附上一张经典图片，该图展示了SQL中常用的连接查询及其语法：

那么这样关于连接查询的学习就到此为止，后续学习其他知识。