连接查询
# 连接查询
从一张表单独查询是单表查询，从多张表联合起来查询就是连接查询。
- 根据语法年代分类
SQL92：1992年的语法；表的连接条件和后期筛选条件都放在where中导致结构不清晰。
SQL99：1999年的语法。连接条件和筛选条件是分开的，结构清晰。
1
2
2
- 根据表的连接方式分类
内连接:有等值连接、非等值连接和自连接三种。
外连接:左外连接和右外连接。
全连接:不常用。
1
2
3
2
3
# 两张表直接连接查询-没有任何条件限制
最终出现的结果是两张表条数的乘积。也叫笛卡尔乘积现象。
mysql> select * from department;
+----+-------------+-----------------+
| id | depart_name | depart_location |
+----+-------------+-----------------+
| 1 | 研发部 | 天津 |
| 2 | 销售部 | 上海 |
+----+-------------+-----------------+
2 rows in set (0.00 sec)
mysql> select * from employ;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| id | name | age | gender | birthday | salary | subsidy | overtimepay | depart_id |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
| 1 | 王维 | 33 | 男 | 1923-01-01 00:00:00 | 98 | 87 | 98 | 1 |
| 2 | 李白 | 43 | 女 | 1913-01-01 00:00:00 | 88 | 89 | 98 | 2 |
| 3 | 杜甫 | 34 | 男 | 1929-01-01 00:00:00 | 96 | 79 | 98 | 1 |
| 4 | 郭靖 | 36 | 男 | 1923-01-01 00:00:00 | 96 | 99 | 98 | 2 |
| 5 | 王良 | 66 | 女 | 1943-01-01 00:00:00 | 90 | 88 | 98 | 1 |
| 6 | 刘邦 | 46 | 男 | 1953-01-01 00:00:00 | 93 | 88 | 98 | 2 |
| 7 | 王维 | 33 | 男 | 1923-01-01 00:00:00 | 98 | 87 | 98 | 1 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+
7 rows in set (0.00 sec)
mysql> select * from employ,department;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+----+-------------+-----------------+
| id | name | age | gender | birthday | salary | subsidy | overtimepay | depart_id | id | depart_name | depart_location |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+----+-------------+-----------------+
| 1 | 王维 | 33 | 男 | 1923-01-01 00:00:00 | 98 | 87 | 98 | 1 | 1 | 研发部 | 天津 |
| 1 | 王维 | 33 | 男 | 1923-01-01 00:00:00 | 98 | 87 | 98 | 1 | 2 | 销售部 | 上海 |
| 2 | 李白 | 43 | 女 | 1913-01-01 00:00:00 | 88 | 89 | 98 | 2 | 1 | 研发部 | 天津 |
| 2 | 李白 | 43 | 女 | 1913-01-01 00:00:00 | 88 | 89 | 98 | 2 | 2 | 销售部 | 上海 |
| 3 | 杜甫 | 34 | 男 | 1929-01-01 00:00:00 | 96 | 79 | 98 | 1 | 1 | 研发部 | 天津 |
| 3 | 杜甫 | 34 | 男 | 1929-01-01 00:00:00 | 96 | 79 | 98 | 1 | 2 | 销售部 | 上海 |
| 4 | 郭靖 | 36 | 男 | 1923-01-01 00:00:00 | 96 | 99 | 98 | 2 | 1 | 研发部 | 天津 |
| 4 | 郭靖 | 36 | 男 | 1923-01-01 00:00:00 | 96 | 99 | 98 | 2 | 2 | 销售部 | 上海 |
| 5 | 王良 | 66 | 女 | 1943-01-01 00:00:00 | 90 | 88 | 98 | 1 | 1 | 研发部 | 天津 |
| 5 | 王良 | 66 | 女 | 1943-01-01 00:00:00 | 90 | 88 | 98 | 1 | 2 | 销售部 | 上海 |
| 6 | 刘邦 | 46 | 男 | 1953-01-01 00:00:00 | 93 | 88 | 98 | 2 | 1 | 研发部 | 天津 |
| 6 | 刘邦 | 46 | 男 | 1953-01-01 00:00:00 | 93 | 88 | 98 | 2 | 2 | 销售部 | 上海 |
| 7 | 王维 | 33 | 男 | 1923-01-01 00:00:00 | 98 | 87 | 98 | 1 | 1 | 研发部 | 天津 |
| 7 | 王维 | 33 | 男 | 1923-01-01 00:00:00 | 98 | 87 | 98 | 1 | 2 | 销售部 | 上海 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+----+-------------+-----------------+
14 rows in set (0.00 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
31
32
33
34
35
36
37
38
39
40
41
42
43
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
# 有条件的连接查询
mysql> select * from employ,department where employ.depart_id = department.id;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+----+-------------+-----------------+
| id | name | age | gender | birthday | salary | subsidy | overtimepay | depart_id | id | depart_name | depart_location |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+----+-------------+-----------------+
| 1 | 王维 | 33 | 男 | 1923-01-01 00:00:00 | 98 | 87 | 98 | 1 | 1 | 研发部 | 天津 |
| 3 | 杜甫 | 34 | 男 | 1929-01-01 00:00:00 | 96 | 79 | 98 | 1 | 1 | 研发部 | 天津 |
| 5 | 王良 | 66 | 女 | 1943-01-01 00:00:00 | 90 | 88 | 98 | 1 | 1 | 研发部 | 天津 |
| 7 | 王维 | 33 | 男 | 1923-01-01 00:00:00 | 98 | 87 | 98 | 1 | 1 | 研发部 | 天津 |
| 2 | 李白 | 43 | 女 | 1913-01-01 00:00:00 | 88 | 89 | 98 | 2 | 2 | 销售部 | 上海 |
| 4 | 郭靖 | 36 | 男 | 1923-01-01 00:00:00 | 96 | 99 | 98 | 2 | 2 | 销售部 | 上海 |
| 6 | 刘邦 | 46 | 男 | 1953-01-01 00:00:00 | 93 | 88 | 98 | 2 | 2 | 销售部 | 上海 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+----+-------------+-----------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
给表起别名和给字段起别名一样。
mysql> select * from employ e,department d where e.depart_id = d.id;
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+----+-------------+-----------------+
| id | name | age | gender | birthday | salary | subsidy | overtimepay | depart_id | id | depart_name | depart_location |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+----+-------------+-----------------+
| 1 | 王维 | 33 | 男 | 1923-01-01 00:00:00 | 98 | 87 | 98 | 1 | 1 | 研发部 | 天津 |
| 3 | 杜甫 | 34 | 男 | 1929-01-01 00:00:00 | 96 | 79 | 98 | 1 | 1 | 研发部 | 天津 |
| 5 | 王良 | 66 | 女 | 1943-01-01 00:00:00 | 90 | 88 | 98 | 1 | 1 | 研发部 | 天津 |
| 7 | 王维 | 33 | 男 | 1923-01-01 00:00:00 | 98 | 87 | 98 | 1 | 1 | 研发部 | 天津 |
| 2 | 李白 | 43 | 女 | 1913-01-01 00:00:00 | 88 | 89 | 98 | 2 | 2 | 销售部 | 上海 |
| 4 | 郭靖 | 36 | 男 | 1923-01-01 00:00:00 | 96 | 99 | 98 | 2 | 2 | 销售部 | 上海 |
| 6 | 刘邦 | 46 | 男 | 1953-01-01 00:00:00 | 93 | 88 | 98 | 2 | 2 | 销售部 | 上海 |
+----+------+------+--------+---------------------+--------+---------+-------------+-----------+----+-------------+-----------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
要尽量减少表的连接次数，不然查询的数据量会激增。
通过笛卡尔乘积现象可以得知，表的连接次数越多效率越低。
# 内连接之等值连接
SQL92语法
mysql> select e.name,d.depart_name from employ e,department d where e.depart_id = d.id;
+------+-------------+
| name | depart_name |
+------+-------------+
| 王维 | 研发部 |
| 杜甫 | 研发部 |
| 王良 | 研发部 |
| 王维 | 研发部 |
| 李白 | 销售部 |
| 郭靖 | 销售部 |
| 刘邦 | 销售部 |
+------+-------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL99语法
mysql> select e.name,d.depart_name from employ e inner join department d on e.depart_id = d.id;
+------+-------------+
| name | depart_name |
+------+-------------+
| 王维 | 研发部 |
| 杜甫 | 研发部 |
| 王良 | 研发部 |
| 王维 | 研发部 |
| 李白 | 销售部 |
| 郭靖 | 销售部 |
| 刘邦 | 销售部 |
+------+-------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
inner是可以省略不写的。带着可读性更好。
加条件 on 是为了只显示有效数据。
SQL99语法
mysql> select e.name,d.depart_name from employ e inner join department d on e.depart_id = d.id;
+------+-------------+
| name | depart_name |
+------+-------------+
| 王维 | 研发部 |
| 杜甫 | 研发部 |
| 王良 | 研发部 |
| 王维 | 研发部 |
| 李白 | 销售部 |
| 郭靖 | 销售部 |
| 刘邦 | 销售部 |
+------+-------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 内连接之非等值连接
mysql> select e.salary,e.name,s.math,s.chinese from student s inner join employ e on e.salary > s.math and e.salary < s.chinese;
+--------+------+------+---------+
| salary | name | math | chinese |
+--------+------+------+---------+
| 98 | 王维 | 88 | 99 |
| 96 | 杜甫 | 88 | 99 |
| 96 | 郭靖 | 88 | 99 |
| 90 | 王良 | 88 | 99 |
| 93 | 刘邦 | 88 | 99 |
| 98 | 王维 | 88 | 99 |
| 96 | 杜甫 | 90 | 97 |
| 96 | 郭靖 | 90 | 97 |
| 93 | 刘邦 | 90 | 97 |
+--------+------+------+---------+
9 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
2
3
4
5
6
7
8
9
10
11
12
13
14
15
# 内连接之自连接
mysql> select a.name as '学生姓名', b.name as '老师姓名' from student a inner join student b on a.math = b.english;
+-----------+----------+
| 学生姓名 | 老师姓名 |
+-----------+----------+
| 李白 | 王良 |
| WANGGONG | 王良 |
| WANG GONG | 王良 |
| 李白 | 刘邦 |
| WANGGONG | 刘邦 |
| WANG GONG | 刘邦 |
+-----------+----------+
6 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
2
3
4
5
6
7
8
9
10
11
12
# 右外连
mysql> select e.name,d.depart_name from employ e right outer join department d on e.depart_id = d.id;
+------+-------------+
| name | depart_name |
+------+-------------+
| 王维 | 研发部 |
| 杜甫 | 研发部 |
| 王良 | 研发部 |
| 王维 | 研发部 |
| 李白 | 销售部 |
| 郭靖 | 销售部 |
| 刘邦 | 销售部 |
+------+-------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
右边的表看出主表，需要将该表的数据都查询出来。捎带着关联查询左边的表。outer 可以省略，带着可读性强。
外连接的条数一定是大于等于内连接的条数。
# 左外连
mysql> select e.name,d.depart_name from department d left outer join employ e on e.depart_id = d.id;
+------+-------------+
| name | depart_name |
+------+-------------+
| 王维 | 研发部 |
| 杜甫 | 研发部 |
| 王良 | 研发部 |
| 王维 | 研发部 |
| 李白 | 销售部 |
| 郭靖 | 销售部 |
| 刘邦 | 销售部 |
+------+-------------+
7 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
2
3
4
5
6
7
8
9
10
11
12
13
编辑 (opens new window)
上次更新: 2025/02/10, 20:20:37