13.2.12 多表连接查询 1. SQL92的连接查询

发表于 更新于 分类于 ， ， Waline： 本文字数： 7.7k 阅读时长 ≈ 7 分钟

13.2.12 多表连接查询

很多时候,需要选择的数据并不是来自一个表,而是来自多个数据表,这就需要使用多表连接查询。例如,对于上面的student_table和teacher_table两个数据表,如果希望查询出所有学生以及他的老师名字,这就需要从两个表中取数据。

两种连接规范

SQL92

多表连接查询有两种规范,较早的SQL92规范支持如下几种多表连接查询。

  • 等值连接。
  • 非等值连接。
  • 外连接。
  • 广义笛卡儿积。

SQL99

SQL99规范提供了可读性更好的多表连接语法,并提供了更多类型的连接查询。SQL99支持如下几种多表连接查询。

  • 交叉连接。
  • 自然连接。
  • 使用using子句的连接。
  • 使用on子句的连接。
  • 全外连接或者左、右外连接。

1. SQL92的连接查询

SQL92的多表连接语法比较简洁,这种语法把多个数据表都放在from之后,多个表之间以逗号隔开;连接条件放在where之后,与查询条件之间用and逻辑运算符连接。

  • 如果连接条件要求两列值相等,则称为等值连接,否则称为非等值连接;
  • 如果没有任何连接条件,则称为广义笛卡儿积。

语法格式

SQL92中多表连接查询的语法格式如下:

1
2
3
select column1,column2,...
from table1,table2....
[where join_condition]

列同名的情况

多表连接查询中可能出现两个或多个数据列具有相同的列名,则需要在这些同名列之间使用表名前缀或表别名前缀作为限制,避免系统混淆。
实际上,所有的列都可以增加表名前缀或表别名前缀。只是进行单表查询时,绝不可能出现同名列,所以系统不可能混淆,因此通常省略表名前缀。
如下SQL语句查询出所有学生的资料以及对应的老师姓名。

1
2
3
4
5
select s.*,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 使用where指定连接条件
where s.java_teacher=t.teacher_id;

查询结果:

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
31
32
33
34
35
36
37
38
39
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select s.*,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 使用where指定连接条件
where s.java_teacher=t.teacher_id;
+------------+--------------+--------------+--------------+
| student_id | student_name | java_teacher | teacher_name |
+------------+--------------+--------------+--------------+
|          1 | 张三         |            1 | Yeeku        |
|          2 | 张三         |            1 | Yeeku        |
|          3 | 李四         |            1 | Yeeku        |
|          4 | 王五         |            2 | Leegang      |
|          5 | _王五        |            2 | Leegang      |
|          6 | NULL         |            2 | Leegang      |
|          7 | 赵六         |            3 | Martine      |
+------------+--------------+--------------+--------------+
7 rows in set

上面的查询结果正好满足要求,可以看到每个学生以及他对应的老师的名字。

伪代码

实际上,多表查询的过程可理解成一个嵌套循环,这个嵌套循环的伪码如下:

1
2
3
4
5
6
7
8
9
10
11
12
//依次遍历teacher_table表中的每条记录
for t in teacher_table
{
    // 遍历 student_table表中的每条记录
    for s in student_table
    {
        if(s.java_teacher=t.teacher_id)
        {
            output s+t
        }
    }
}

广义笛卡尔积

理解了上面的伪码之后,接下来即可很轻易地理解多表连接查询的运行机制。如果求广义笛卡儿积,则where子句后没有任何连接条件,相当于没有上面的if语句,广义笛卡儿积的结果会有n×m条记录。只要把where后的连接条件去掉,就可以得到广义笛卡儿积,如下SQL语句所示:

1
2
3
4
#不使用连接条件,得到广义笛卡尔积
select s.*,teacher_name
#指定多个数据表,并指定表别名
from student_table s,teacher_table t;

运行效果如下:

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
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> #不使用连接条件,得到广义笛卡尔积
select s.*,teacher_name
#指定多个数据表,并指定表别名
from student_table s,teacher_table t;
+------------+--------------+--------------+--------------+
| student_id | student_name | java_teacher | teacher_name |
+------------+--------------+--------------+--------------+
|          1 | 张三         |            1 | Yeeku        |
|          1 | 张三         |            1 | Leegang      |
|          1 | 张三         |            1 | Martine      |
|          2 | 张三         |            1 | Yeeku        |
|          2 | 张三         |            1 | Leegang      |
|          2 | 张三         |            1 | Martine      |
|          3 | 李四         |            1 | Yeeku        |
|          3 | 李四         |            1 | Leegang      |
|          3 | 李四         |            1 | Martine      |
|          4 | 王五         |            2 | Yeeku        |
|          4 | 王五         |            2 | Leegang      |
|          4 | 王五         |            2 | Martine      |
|          5 | _王五        |            2 | Yeeku        |
|          5 | _王五        |            2 | Leegang      |
|          5 | _王五        |            2 | Martine      |
|          6 | NULL         |            2 | Yeeku        |
|          6 | NULL         |            2 | Leegang      |
|          6 | NULL         |            2 | Martine      |
|          7 | 赵六         |            3 | Yeeku        |
|          7 | 赵六         |            3 | Leegang      |
|          7 | 赵六         |            3 | Martine      |
+------------+--------------+--------------+--------------+
21 rows in set

与此类似的是,非等值连接的执行结果可以使用上面的嵌套循环来计算,如下SQL语句所示:

1
2
3
4
5
select s.*,teacher_id,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 非等值连接
where s.java_teacher>t.teacher_id;

运行结果：

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
31
32
33
34
35
36
37
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select s.*,teacher_id,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 非等值连接
where s.java_teacher>t.teacher_id;
+------------+--------------+--------------+------------+--------------+
| student_id | student_name | java_teacher | teacher_id | teacher_name |
+------------+--------------+--------------+------------+--------------+
|          4 | 王五         |            2 |          1 | Yeeku        |
|          5 | _王五        |            2 |          1 | Yeeku        |
|          6 | NULL         |            2 |          1 | Yeeku        |
|          7 | 赵六         |            3 |          1 | Yeeku        |
|          7 | 赵六         |            3 |          2 | Leegang      |
+------------+--------------+--------------+------------+--------------+
5 rows in set

上面SQL语句的执行结果相当于if条件换成了s.java_teacher>t.teacher_id。
如果还需要对记录进行过滤,则将过滤条件和连接条件使用and连接起来,如下SQL语句所示:

1
2
3
4
5
select s.*,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 使用where指定连接条件,并指定student_name列不能不为null
where s.java_teacher=t.teacher_id and student_name is not null;

运行结果:

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
31
32
33
34
35
36
37
38
mysql> select * from student_table;
+------------+--------------+--------------+
| student_id | student_name | java_teacher |
+------------+--------------+--------------+
|          1 | 张三         |            1 |
|          2 | 张三         |            1 |
|          3 | 李四         |            1 |
|          4 | 王五         |            2 |
|          5 | _王五        |            2 |
|          6 | NULL         |            2 |
|          7 | 赵六         |            3 |
+------------+--------------+--------------+
7 rows in set
mysql> select * from teacher_table;
+------------+--------------+
| teacher_id | teacher_name |
+------------+--------------+
|          1 | Yeeku        |
|          2 | Leegang      |
|          3 | Martine      |
+------------+--------------+
3 rows in set
mysql> select s.*,teacher_name
# 指定多个数据表,并指定表别名
from student_table s,teacher_table t
# 使用where指定连接条件,并指定student_name列不能不为null
where s.java_teacher=t.teacher_id and student_name is not null;
+------------+--------------+--------------+--------------+
| student_id | student_name | java_teacher | teacher_name |
+------------+--------------+--------------+--------------+
|          1 | 张三         |            1 | Yeeku        |
|          2 | 张三         |            1 | Yeeku        |
|          3 | 李四         |            1 | Yeeku        |
|          4 | 王五         |            2 | Leegang      |
|          5 | _王五        |            2 | Leegang      |
|          7 | 赵六         |            3 | Martine      |
+------------+--------------+--------------+--------------+
6 rows in set

外连接

虽然**MySQL不支持SQL92中的左外连接、右外连接**,但本书还是有必要了解一下SQL92中的左外连接和右外连接。SQL92中的外连接就是在连接条件的列名后增加括号包起来的外连接符(+或*,不同的数据库有一定的区别),当外连接符出现在左边时称为左外连接,出现在右边时则称为右外连接。如下SQL语句所示:

1
2
3
4
select s.*,teacher_name
from student_table s,teacher_table t
# 右外连接
where s.java_teacher=t.teacher_id(*);

外连接就是在外连接符所在的表中增加一个”万能行”,这行记录的所有数据都是null,而且该行可以与另一个表中所有不满足条件的记录进行匹配,通过这种方式就可以把另一个表中的所有记录选出来,不管这些记录是否满足连接条件。

自连接

除此之外,还有一种自连接,正如前面介绍外键约束时提到的自关联,如果同一个表中表中的不同记录之间存在主、外键约束关联,例如把员工、经理保存在同一个表里,则需要使用自连接查询。
注意
自连接只是连接的一种用法,并不是一种连接类型,不管是SQL92还是SQL99都可以使用自连接查询。自连接的本质就是把一个表当成两个表来用。
下面的SQL语句建立了一个自关联的数据表,并向表中插入了4条数据。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
create table emp_table
(
    emp_id int auto_increment primary key,
    emp_name varchar(255),
    manager_id int,
    # 自关联
    foreign key(manager_id) references emp_table(emp_id)
);
# 简写形式insert,要给所有列赋值,
# 第一列自增长,可以随意赋值,这里赋值null
insert into emp_table
values(null,'唐僧',null);
insert into emp_table
values(null,'孙悟空',1);
insert into emp_table
values(null,'猪八戒',1);
insert into emp_table
values(null,'沙僧',1);

如果需要查询该数据表中的所有员工名,以及每个员工对应的经理名,则必须使用自连接查询。所谓自连接就是把一个表当成两个表来用,这就需要为一个表起两个别名,而且查询中用的所有数据列都要加表别名前缀,因为两个表的数据列完全一样。下面的自连接查询可以查询出所有的员工名,以及对应的经理名。

1
2
3
select emp.emp_id,emp.emp_name 员工名,mgr.emp_name 经理名
from emp_table emp,emp_table mgr
where emp.manager_id=mgr.emp_id;

查询结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql> select * from emp_table;
+--------+----------+------------+
| emp_id | emp_name | manager_id |
+--------+----------+------------+
|      1 | 唐僧     | NULL       |
|      2 | 孙悟空   |          1 |
|      3 | 猪八戒   |          1 |
|      4 | 沙僧     |          1 |
+--------+----------+------------+
4 rows in set
mysql> select emp.emp_id,emp.emp_name 员工名,mgr.emp_name 经理名
from emp_table emp,emp_table mgr
where emp.manager_id=mgr.emp_id;
+--------+--------+--------+
| emp_id | 员工名 | 经理名 |
+--------+--------+--------+
|      2 | 孙悟空 | 唐僧   |
|      3 | 猪八戒 | 唐僧   |
|      4 | 沙僧   | 唐僧   |
+--------+--------+--------+
3 rows in set
  1. SQL99的连接查询