13.2.12 多表连接查询 2. SQL99的连接查询

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

13.2.12 多表连接查询 2. SQL99的连接查询

SQL99的连接查询与SQL92的连接查询原理基本相似,不同的是SQL99连接查询的可读性更强——查询用的多个数据表显式使用xxx join连接,而不是直接依次排列在from之后,from后只需要放一个数据表;连接条件不再放在where之后,而是提供了专门的连接条件子句。

交叉连接

交叉连接(cross join):交叉连接效果就是SQL92中的广义笛卡儿积,所以交叉连接无须任何连接条件,如下SQL语句所示:

1
2
3
4
5
select s.*,teacher_name
# SQL99多表连接查询from后只有一个表名
from student_table s
#cross join交叉连接,相当于广义笛卡尔积
cross join 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
51
52
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
# SQL99多表连接查询from后只有一个表名
from student_table s
#cross join交叉连接,相当于广义笛卡尔积
cross join 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      |
+------------+--------------+--------------+--------------+

自然连接

自然连接(natural join):自然连接表面上看起来也无须指定连接条件,但自然连接是有连接条件的,自然连接会以两个表中的同名列作为连接条件;如果两个表中没有同名列,则自然连接与交叉连接效果完全一样——因为没有连接条件。如下SQL语句所示:

1
2
3
4
5
select s.*,teacher_name
# SQL99多表连接查询from后只有一个表名
from student_table s
# natural join 自然连接使用两个表中的同名列作为连接条件
natural join 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
51
52
53
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
# SQL99多表连接查询from后只有一个表名
from student_table s
# natural join 自然连接使用两个表中的同名列作为连接条件
natural join 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

using子句

using子句连接:using子句可以指定一列或多列,用于显式指定两个表中的同名列作为连接条件。假设两个表中有超过一列的同名列,如果使用natural join,则会把所有的同名列当成连接条件;使用using子句,就可显式指定使用哪些同名列作为连接条件。如下SQL语句所示:

1
2
3
4
5
select s.*,teacher_name
from student_table s
# join 连接另一个表
join teacher_table t
using(teacher_id);

运行结果:

1
2
3
4
5
6
mysql> select s.*,teacher_name
from student_table s
# join 连接另一个表
join teacher_table t
using(teacher_id);
1054 - Unknown column 'teacher_id' in 'from clause'

运行上面语句将出现一个错误,因为student_table表中并不存在名为teacher_id的列。也就是说,如果使用using子句来指定连接条件,则两个表中必须有同名列,否则就会出现错误。

on子句连接

on子句连接:这是最常用的连接方式,SQL99语法的连接条件放在on子句中指定,而且每个on子句只指定一个连接条件。这意味着:如果需要进行N表连接,则需要有N-1个join...on对。如下SQL语句所示:

1
2
3
4
select s.*,teacher_name
from student_table s
join teacher_table t
on 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
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
join teacher_table t
on 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

使用on子句的连接完全可以代替SQL92中的等值连接、非等值连接,因为on子句的连接条件除了等值条件之外,也可以是非等值条件。如下SQL语句就是SQL99中的非等值连接。

1
2
3
4
5
select s.*,teacher_name
from student_table s
join teacher_table t
# 使用on来指定连接条件:非等值连接
on 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
join teacher_table t
# 使用on来指定连接条件:非等值连接
on 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

外连接

左、右、全外连接:这三种外连接分别使用left[outer]join、right[outer]join和full[outer]join,这三种外连接的连接条件一样通过on子句来指定,既可以是等值连接条件,也可以是非等值连接条件。

右外连接

下面使用右外连接,连接条件是非等值连接。

1
2
3
4
select s.*,teacher_id,teacher_name
from student_table s
right join teacher_table t
on 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
40
41
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
right join teacher_table t
on s.java_teacher<t.teacher_id;
+------------+--------------+--------------+------------+--------------+
| student_id | student_name | java_teacher | teacher_id | teacher_name |
+------------+--------------+--------------+------------+--------------+
| NULL       | NULL         | NULL         |          1 | Yeeku        |
|          1 | 张三         |            1 |          2 | Leegang      |
|          2 | 张三         |            1 |          2 | Leegang      |
|          3 | 李四         |            1 |          2 | Leegang      |
|          1 | 张三         |            1 |          3 | Martine      |
|          2 | 张三         |            1 |          3 | Martine      |
|          3 | 李四         |            1 |          3 | Martine      |
|          4 | 王五         |            2 |          3 | Martine      |
|          5 | _王五        |            2 |          3 | Martine      |
|          6 | NULL         |            2 |          3 | Martine      |
+------------+--------------+--------------+------------+--------------+
10 rows in set

左外连接

下面使用左外连接,连接条件是非等值连接。

1
2
3
4
select s.*,teacher_name
from student_table s
left join teacher_table t
on 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
40
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
left join teacher_table t
on s.java_teacher>t.teacher_id;
+------------+--------------+--------------+------------+--------------+
| student_id | student_name | java_teacher | teacher_id | teacher_name |
+------------+--------------+--------------+------------+--------------+
|          1 | 张三         |            1 | NULL       | NULL         |
|          2 | 张三         |            1 | NULL       | NULL         |
|          3 | 李四         |            1 | NULL       | NULL         |
|          4 | 王五         |            2 |          1 | Yeeku        |
|          5 | _王五        |            2 |          1 | Yeeku        |
|          6 | NULL         |            2 |          1 | Yeeku        |
|          7 | 赵六         |            3 |          1 | Yeeku        |
|          7 | 赵六         |            3 |          2 | Leegang      |
+------------+--------------+--------------+------------+--------------+
8 rows in set

运行上面两条外连接语句并查看它们的运行结果,不难发现SQL99外连接与SQL92外连接恰好相反,SQL99左外连接将会把左边表中所有不满足连接条件的记录全部列出;SQL99右外连接将会把右边表中所有不满足连接条件的记录全部列出。

全外连接

下面的SQL语句使用全外连接,连接条件是等值连接。

1
2
3
4
select s.*,teacher_name
from student_table s
full join teacher_table t
on s.java_teacher=t.teacher_id;

运行错误:

1
2
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'full join teacher_table t
on s.java_teacher=t.teacher_id' at line 3

SQL99的全外连接将会把两个表中所有不满足连接条件的记录全部列出。
注意运行上面查询语句时会出现错误,这是因为**MySQL并不支持全外连接**。