0%

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

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-1join...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]joinright[outer]joinfull[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
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并不支持全外连接

原文链接: 13.2.12 多表连接查询 2. SQL99的连接查询