10.5子查询

子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果，可以为外层查询语句提供查询条件。因为在特定情况下，一个査询语句的条件需要另一个查询语句来获取。例如，现在需要从学生成绩表中査询计算机系学生的各科成绩。那么，苜先就必须知道哪些课程是计算机系学生选修的。因此，必须先査询计算机系学生选修的课程，然后根据这些课程来查询计算机系学生的各科成绩。通过子査询，可以实现多表之间的查询。子查询中可能包括故、NOT IN、ANY、ALL、EXISTS和 NOT EXISTS等关键字。子查询中还可能包含比较运算符，如“ =”、“!=”、“>”和“<”等。本小节将详细讲解子查询的知识。

10.5.1带IN关键字的子查询

一个查询语句的条件可能落在另一个SELECT语句的查询结果中。这可以通过IN关键字来判断。例如，要查询哪些同学选择了计算机系开设的课程。先必须从课程表中查询出计算机系开设了哪些课程。然后再从学生表中进行查询。如果学生选修的课程在前面查询出来的课程中，则査询出该同学的信息。这可以用带取关键字的子查询来实现。

【示例10-56】下面查询employee表中的记录。这些记录的d_id字段的值必须在department表中出现过。SELECT语句如下：

SELECT * FROM employee WHERE d_id IN (SELECT d_id FROM department);

在执行该语句之前，先査看一下department表和employee表的情况，以便进行对比。 department表的查询结果如下：

mysql> select * from department;
+------+--------+--------------+---------------+
| d_id | d_name | function     | address       |
+------+--------+--------------+---------------+
| 1001 | 科研部      | 新产品研发          | 3号楼5层           |
| 1002 | 生产部       | 主管生产             | 5号楼1层           |
| 1003 | 销售部      | 负责产品销售            | 1号楼销售大厅           |
+------+--------+--------------+---------------+
3 rows in set (0.00 sec)

查询结果显示，department表中d_id字段取值分别为1001、1002和1003。下面是employee表的查询结果：

mysql> select * from employee;
+-----+------+------+-----+-----+--------------+
| num | d_id | name | age | sex | homeaddr     |
+-----+------+------+-----+-----+--------------+
|   1 | 1001 | 张三     |  26 | 男    | 北京市海淀区            |
|   2 | 1002 | 李四     |  24 | 女   | 北京市昌平区           |
|   3 | 1003 | 王五     |  25 | 男    | 湖南省长沙市           |
|   4 | 1004 | Aric |  15 | 男    | England      |
+-----+------+------+-----+-----+--------------+
4 rows in set (0.00 sec)

查询结果显示，employee表中的d_id字段取值分别为1001、1002和1004。可以看出1004不在 department表中。然后执行带IN关键字的子查询。执行结果如下：

mysql> select * from employee where d_id in(select d_id from department);
+-----+------+------+-----+-----+--------------+
| num | d_id | name | age | sex | homeaddr     |
+-----+------+------+-----+-----+--------------+
|   1 | 1001 | 张三     |  26 | 男    | 北京市海淀区            |
|   2 | 1002 | 李四     |  24 | 女   | 北京市昌平区           |
|   3 | 1003 | 王五     |  25 | 男    | 湖南省长沙市           |
+-----+------+------+-----+-----+--------------+
3 rows in set (0.00 sec)

查询结果中只有d_id值为1001和1002的记录。而d_id值为1004的记录没有被查询出来。这是因为department表中没有任何记录的d_id字段取值为1004。NOT IN关键字的作用与IN关键字刚好相反。

【示例10-57】下面查询employee表中的记录。这些记录的d_id字段的值必须没有在department表中出现过。SELECT语句如下：

SELECT * FROM employee WHERE d_id NOT IN (SELECT d_id FROM department);

语句执行结果如下：

mysql> SELECT * FROM employee WHERE d_id NOT IN (SELECT d_id FROM department);
+-----+------+------+-----+-----+----------+
| num | d_id | name | age | sex | homeaddr |
+-----+------+------+-----+-----+----------+
|   4 | 1004 | Aric |  15 | 男    | England  |
+-----+------+------+-----+-----+----------+

结果中只查询出了d_id值为1004的记录。因为，department表中没有任何记录的d_id 字段取值为1004。

10.5.2 带比较运算符的子查询

子查询可以使用比较运算符。这些比较运算符包括=、！=、>、>=、<、<=和<>等。其中，<>与!=是等价的。比较运算符在子查询时使用的非常广泛。如査询分数、年龄、价格和收入等。

【示例10-58】下面从computer_stu表中査询获得一等奖学金的学生的学号、姓名和分数。各个等级的奖学金的最低分存储在scholarship表中。先查看一下scholarship表和computer_stu表的记录，以便进行对比。scholarship表的查询结果如下：

mysql> select * from scholarship;
+-------+-------+
| level | score |
+-------+-------+
|     1 |    90 |
|     2 |    80 |
|     3 |    70 |
+-------+-------+
3 rows in set (0.00 sec)

查询结果显示，一等奖学金的最低分为90; 二等奖学金的最低分为80;三等奖学金的最低分为70。下面是computer_stu表的查询结果。

mysql> select * from computer_stu;
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1002 | Tom  |    91 |
| 1003 | Jim  |    87 |
| 1004 | Aric |    77 |
| 1005 | Lucy |    65 |
| 1006 | Andy |    99 |
| 1007 | Ada  |    85 |
| 1008 | Jeck |    70 |
+------+------+-------+
8 rows in set (0.00 sec)

查询结果显示表中每个同学的学号、姓名和分数。其中，学号为1002和1006的两个人的分数都大于90。下面来查询谁是一等奖学金的得主。首先必须从scholarship表中查询出一等奖学金要求的最低分。然后再从computer_stu表中查询哪些学生的分数高于这个最低分。SELECT语句如下：

SELECT id, name,score FROM computer_stu WHERE score>=(SELECT score FROM scholarship WHERE level=1);

代码执行结果如下：

mysql> SELECT id, name,score FROM computer_stu WHERE score>=(SELECT score FROM scholarship WHERE level=1);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1002 | Tom  |    91 |
| 1006 | Andy |    99 |
+------+------+-------+
2 rows in set (0.00 sec)

结果显示，id为1002和1006的学生获得了一等奖学金。因为他们成绩分別是91和 99,这都大于一等奖学金的最低分。

【示例10-59】下面在department表中查询哪些部门没有年龄为24岁的员工。员工的年龄存储在employee表中。先查询一下employee表和department表，以便进行对比。查询结果如下：

mysql> select * from employee;
+-----+------+------+-----+-----+--------------+
| num | d_id | name | age | sex | homeaddr     |
+-----+------+------+-----+-----+--------------+
|   1 | 1001 | 张三     |  26 | 男    | 北京市海淀区            |
|   2 | 1002 | 李四     |  24 | 女   | 北京市昌平区           |
|   3 | 1003 | 王五     |  25 | 男    | 湖南省长沙市           |
|   4 | 1004 | Aric |  15 | 男    | England      |
+-----+------+------+-----+-----+--------------+
4 rows in set (0.00 sec)

mysql> select * from department;
+------+--------+--------------+---------------+
| d_id | d_name | function     | address       |
+------+--------+--------------+---------------+
| 1001 | 科研部      | 新产品研发          | 3号楼5层           |
| 1002 | 生产部       | 主管生产             | 5号楼1层           |
| 1003 | 销售部      | 负责产品销售            | 1号楼销售大厅           |
+------+--------+--------------+---------------+
3 rows in set (0.00 sec)

从employee表中可以看出，只有部门号（d_id)为1001的部门有员工的年龄（age) 为24。而部门名称存储在department表中，只有根据部门号来查找相应部门的名称。因此，需要先从employee表中查询哪个人为24岁。取出这个人的部门号。然后在department表中查询与该部门号不同的部门。SELECT语句如下：

SELECT d_id,d_name FROM department WHERE d_id!=(SELECT d_id FROM employee WHERE age=24);

代码执行结果如下：

mysql> SELECT d_id,d_name FROM department WHERE d_id!=(SELECT d_id FROM employee WHERE age=24);
+------+--------+
| d_id | d_name |
+------+--------+
| 1001 | 科研部      |
| 1003 | 销售部      |
+------+--------+
2 rows in set (0.00 sec)

结果显示，“生广部”和“销售部”没有员工是24岁。从上面的employee表中可以看到，d_id为1002的部门中，只有年龄为25岁的员工。由于employee表中没有d_id为 1003的记录，因此“销售部”也是满足例题要求的。本例中用到的比较运算符是!=。运算符0与!=是等价的。

【示例10-60】下面用<>替代!=来完成上一个例子。SQL语句如下:

SELECT d_id,d_name FROM department WHERE d_id<>(SELECT d_id FROM employee WHERE age=24);

代码执行结果如下：

mysql> SELECT d_id,d_name FROM department WHERE d_id<>(SELECT d_id FROM employee WHERE age=24);
+------+--------+
| d_id | d_name |
+------+--------+
| 1001 | 科研部      |
| 1003 | 销售部      |
+------+--------+
2 rows in set (0.00 sec)

结果与上个例子是一样的^本例说明运算符<>与!=是等价的。比较运算符中还有其他的等价情况。例如，！>等价于<=，！<等价于>=。

10.5.3 带EXISTS关键字的子查询

EXISTS关键字表示存在。使用EXISTS关键字时，内层查询语句不返回查询的记录。而是返回一个真假值。如果内层查询语句査询到满足条件的记录，就返回一个真值（true), 否则，将返回一个假值（false)。当返回的值为tme时，外层查询语句将进行查询；当返回的为false时，外层查询语句不进行查询或者查询不出任何记录。

【示例10-61】下面如果department表中存在d_id取值为1003的记录,则查询employee表的记录。Select语句如下：

SELECT * FROM employee WHERE EXISTS(SELECT d_name FROM department WHERE d_id=1003);

代码执行结果如下：

mysql> SELECT * FROM employee WHERE EXISTS(SELECT d_name FROM department WHERE d_id=1003);
+-----+------+------+-----+-----+--------------+
| num | d_id | name | age | sex | homeaddr     |
+-----+------+------+-----+-----+--------------+
|   1 | 1001 | 张三     |  26 | 男    | 北京市海淀区            |
|   2 | 1002 | 李四     |  24 | 女   | 北京市昌平区           |
|   3 | 1003 | 王五     |  25 | 男    | 湖南省长沙市           |
|   4 | 1004 | Aric |  15 | 男    | England      |
+-----+------+------+-----+-----+--------------+
4 rows in set (0.00 sec)

结果显示，查询出了employee表中的所有记录。因为department表中存在d_id值为1003的记录，内层查询语句返回一个true。外层查询语句接收的true，开始查询employee表中的记录。因为没有设置查询employee表的查询条件，所以查询出了employee表的所有记录。

【示例10-62】下面如果department表中存在d_id取值为1004的记录,则查询emplouyee表的记录。SELECT语句如下：

SELECT * FROM employee WHERE EXISTS(SELECT d_name FROM department WHERE d_id=1004);

结果显示，没有查询出任何记录。只是因为department表中根本不存在d_id等于1004的记录。内层查询语句返回一个false。外层查询语句接收到false后，不进行任何查询。所以，才没有查询出任何记录。

当然，EXISTS关键字可以与其他的查询条件一起使用。条件表达式与EXISTS关键字之间用AND或者OR来连接。

【示例10-63】下面如果department表中存在d_id取值为1003的记录，则查询employee表中age大于24的记录。SELECT语句如下：

SELECT * FROM employee WHERE age>24 AND EXISTS (SELECT d_name FROM department WHERE d_id=1003);

代码执行结果如下：

mysql> SELECT * FROM employee WHERE age>24 AND EXISTS (SELECT d_name FROM department WHERE d_id=1003);
+-----+------+------+-----+-----+--------------+
| num | d_id | name | age | sex | homeaddr     |
+-----+------+------+-----+-----+--------------+
|   1 | 1001 | 张三     |  26 | 男    | 北京市海淀区            |
|   3 | 1003 | 王五     |  25 | 男    | 湖南省长沙市           |
+-----+------+------+-----+-----+--------------+
2 rows in set (0.00 sec)

结果显示，从employee表中查询出了两条记录。这两条记录的age字段的取值分别是 25和26。因为，当内层查询语句从460&代@6也表中查询到记录，返回一个true。外层查询语句开始进行查询。根据查询条件，从employee表中查询出age大于24的两条记录。

NOT EXISTS与EXISTS刚好相反。使用NOT EXISTS关键字时，当返回的值是true时，外层查询语句不进行查询或者查询不出任何记录。当返回值是false时，外层查询语句将进行查询。

【示例10-64】下面如果department表中不存在d_id取值为1003的记录，则查询employee表的记录。SELECT语句如下：

SELECT * FROM employee WHERE NOT EXISTS(SELECT d_name FROM department WHERE d_id=1003);

代码执行结果如下：

Empty set (0.00 sec)

结果显示，没有查询出任何记录。因为department表中存在d_id为1003的记录，内层查询语句返回了一个true。外层查询语句接收到true后，将不从employee表中查询记录。

注意：EXISTS关键字与前面的关键字很不一样。使用EXISTS关键字时，内层查询语句只返回true和false。如果内层查询语句查询到记录，那么返回加true，否则，将返回false。如果返回true，那么就可以执行外层查询语句。使用前面介绍的其他关键字时，其内层查询语句都会返回查询到的记录。

10.5.4 带ANY关键字的子查询

ANY关键字表示满足其中任一条件。使用ANY关键字时，只要满足内层查询语句返回的结果中的任何一个，就可以通过该条件来执行外层查询语句。例如，需要查询哪些同学能够获得奖学金。那么，首先必须从奖学金表中查询出各种奖学金要求的最低分。只要一个同学的成绩高于不同奖学金最低分的任何一个，这个同学就可以获得奖学金。ANY关键字通常与比较运算符一起使用。例如，>ANY表示大于任何一个值，=ANY表示等于任何一个值。

【示例10-65】下面从computer_stu表中查询出哪些同学可以获得奖学金。奖学金的信息存储在scholarship表中。先查看一下computer_stu表和scholarship表。查询结果如下:

mysql> select * from computer_stu;
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1002 | Tom  |    91 |
| 1003 | Jim  |    87 |
| 1004 | Aric |    77 |
| 1005 | Lucy |    65 |
| 1006 | Andy |    99 |
| 1007 | Ada  |    85 |
| 1008 | Jeck |    70 |
+------+------+-------+
8 rows in set (0.00 sec)

mysql> select * from scholarship;
+-------+-------+
| level | score |
+-------+-------+
|     1 |    90 |
|     2 |    80 |
|     3 |    70 |
+-------+-------+
3 rows in set (0.00 sec)

下面来查询到底谁能得奖学金。先需要从scholarship表中查询出各种奖学金的最低分。然后，从computer_stu表中查询哪些人的分数高于其中任何一个奖学金的最低分。SELECT 语句代码如下：

SELECT * FROM computer_stu WHERE score>=ANY(SELECT score FROM scholarship);

代码执行结果如下：

mysql> SELECT * FROM computer_stu WHERE score>=ANY(SELECT score FROM scholarship);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1001 | Lily |    85 |
| 1002 | Tom  |    91 |
| 1003 | Jim  |    87 |
| 1004 | Aric |    77 |
| 1006 | Andy |    99 |
| 1007 | Ada  |    85 |
| 1008 | Jeck |    70 |
+------+------+-------+
7 rows in set (0.00 sec)

结果显示，有7个人可以获得奖学金。只有id为1005的学生没有获得奖学金。因为，他的分数为65，不髙于奖学金指定最低分的任何一个。

10.5.5带ALL关键字的查询

ALL关键字表示满足所有条件。使用ALL关键字时，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。例如，需要查询哪些同学能够获得一等奖学金。首先必须从奖学金表中查询出各种奖学金要求的最低分。因为一等奖学金要求的分数最高。只有当同学的成绩高于所有奖学金最低分时，这个同学才可能获得一等奖学金。ALL关键字也经常与比较运算符一起使用。例如，>ALL表示大于所有值，<all表示小于所有值。< p="">

【示例10-66】下面从computer_stu表中查询出哪些同学可以获得一等奖学金。奖学金的信息存储在scholarship表中。先需要从scholarship表中查询出各种奖学金的最低分。然后，从computer_stu表中查询哪些人的分数高于所有奖学金的最低分。SELECT语句代码如下：

SELECT * FROM computer_stu WHERE score>=ALL(SELECT score FROM scholarship);

代码执行结果如下：

mysql> SELECT * FROM computer_stu WHERE score>=ALL(SELECT score FROM scholarship);
+------+------+-------+
| id   | name | score |
+------+------+-------+
| 1002 | Tom  |    91 |
| 1006 | Andy |    99 |
+------+------+-------+
2 rows in set (0.00 sec)

结果显示，只有两个人可以获得一等奖学金。因为这两个人的分数比所有奖学金要求的分数都高。

注意：ANY关键字和ALL关键字的使用方式是一样的，但是这两者有很大的区别。使用ANY关键字时，只要满足内层查询语句返回的结果中的任何一个，就可以通过该条件来执行外层查询语句。而ALL关键字刚好相反，只有满足内层查询语句返回的所有结果，才可以执行外层查询语句。

« 10.4 连接查询

10.6合并查询结果»

Mysql教程

第10章查询数据

插入、更新与删除数据

MySQL运算符

MySQL函数

存储过程和函数

10.5子查询

10.5.1带IN关键字的子查询

10.5.2 带比较运算符的子查询

10.5.3 带EXISTS关键字的子查询

10.5.4 带ANY关键字的子查询

10.5.5带ALL关键字的查询

关注微信获取最新动态

分类导航

联动北方技术论坛

智能运维平台

关注微信

Mysql教程

第10章 查询数据

插入、更新与删除数据

MySQL运算符

MySQL函数

存储过程和函数

10.5子查询

10.5.1带IN关键字的子查询

10.5.2 带比较运算符的子查询

10.5.3 带EXISTS关键字的子查询

10.5.4 带ANY关键字的子查询

10.5.5带ALL关键字的查询

关注微信获取最新动态

分类导航

联动北方技术论坛

智能运维平台

关注微信

第10章查询数据