多行子查询

多行子查询即是子查询的返回结果是多行数据。当主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，ALL, ANY）来进行比较。

其中

IN 的含义是匹配子查询结果中的任一个值即可（"IN" 操作符，能够测试某个值是否在一个列表中）

ALL 则必须要符合子查询的所有值才可

ANY 要符合子查询结果的任何一个值即可。

而且须注意ALL 和ANY 操作符不能单独使用，而只能与单行比较符（=、>、< 、>= 、<= 、<>）结合使用。

1. 多行子查询使用IN操作符号

1.1 实例

查询国籍为'USA'的所有教师所授课程名称：

SELECT name
FROM   courses
WHERE  teacher_id IN 
       ( SELECT id 
         FROM teachers 
         WHERE country='USA');

执行查询结果为：

mysql> SELECT name
    -> FROM   courses
    -> WHERE  teacher_id IN
    ->        ( SELECT id
    ->          FROM teachers
    ->          WHERE country='USA');
+-------------------------+
| name                    |
+-------------------------+
| System Design           |
| Django                  |
| Artificial Intelligence |
| Java P6+                |
+-------------------------+
4 rows in set (0.00 sec)

1.2 总结

通过前面的实例学习，我们总结了带有IN的SELECT子查询方法

语法

SELECT column_name(s)
FROM table_name
WHERE column_name in
   (SELECT column_name
   FROM table_name)

练习题

查询所有年龄大于20岁的老师所教的所有课程的课程名。

SELECT name
FROM courses
WHERE teacher_id IN 
    (SELECT id
     FROM teachers
     WHERE age > 20);

目标输出结果

mysql> SELECT name
    -> FROM courses
    -> WHERE teacher_id IN
    ->  (SELECT id
    ->      FROM teachers
    ->      WHERE age > 20);
+-------------------------+
| name                    |
+-------------------------+
| Advanced Algorithms     |
| System Design           |
| Django                  |
| Web                     |
| Artificial Intelligence |
| Java P6+                |
| Object Oriented Design  |
+-------------------------+
7 rows in set (0.00 sec)

2. 多行子查询使用ANY操作符

ANY 要符合子查询结果的任何一个值即可

2.1 实例

使用课程表courses和教师表teachers

查询有一门授课学生上课人数超过“Southern Emperor”的任意一门授课人数的教师姓名

SELECT name
FROM teachers
WHERE id IN
    (SELECT DISTINCT teacher_id
     FROM courses
     WHERE student_count > ANY( SELECT student_count
                                FROM courses
                                WHERE teacher_id = ( SELECT id
                                                     FROM teachers
                                                     WHERE name = 'Southern Emperor') )
     ) AND
     ( id <> (SELECT id
              FROM teachers
              WHERE name = 'Southern Emperor') );

执行输出结果

mysql> SELECT name
    -> FROM teachers
    -> WHERE id IN
    ->  (SELECT DISTINCT teacher_id
    ->      FROM courses
    ->      WHERE student_count > ANY( SELECT student_count
    ->                                 FROM courses
    ->                                 WHERE teacher_id = ( SELECT id
    ->                                                      FROM teachers
    ->                                                      WHERE name = 'Southern Emperor') )
    ->      ) AND
    ->      ( id <> (SELECT id
    ->               FROM teachers
    ->               WHERE name = 'Southern Emperor') );
+-----------------+
| name            |
+-----------------+
| Eastern Heretic |
| Western Venom   |
+-----------------+
2 rows in set (0.02 sec)

上面的SQL语句比较长，但是拆开理解就会非常容易。

2.1 总结

通过以上实例学习我们可以总结使用ANY的多行子查询使用方法

语法

SELECT column_name(s)
FROM table_name
WHERE column_name in
   ANY(SELECT column_name
   FROM table_name)

练习题

查询有一门课程授课开课时间大于‘Western Venom’教师任意一门开课时间的教师姓名

3. 多行子查询使用ALL操作符

ALL 则必须要符合子查询的所有值才可

3.1 实例

使用课程表courses和教师表teachers

查询有一门授课学生上课人数超过“Western Venom”的所有授课人数的教师姓名

SELECT name
FROM teachers
WHERE id IN
    (SELECT DISTINCT teacher_id
     FROM courses
     WHERE student_count > ALL( SELECT student_count
                                FROM courses
                                WHERE teacher_id = ( SELECT id
                                                     FROM teachers
                                                     WHERE name = 'Western Venom') )
     );

执行输出结果

mysql> SELECT name
    -> FROM teachers
    -> WHERE id IN
    ->  (SELECT DISTINCT teacher_id
    ->      FROM courses
    ->      WHERE student_count > ALL( SELECT student_count
    ->                                 FROM courses
    ->                                 WHERE teacher_id = ( SELECT id
    ->                                                      FROM teachers
    ->                                                      WHERE name = 'Western Venom') )
    ->      );
+-----------------+
| name            |
+-----------------+
| Eastern Heretic |
+-----------------+
1 row in set (0.00 sec)

3.2 总结

通过以上实例学习，我们可以总结ALL的使用方法

语法

SELECT column_name(s)
FROM table_name
WHERE column_name in
   ALL(SELECT column_name
   FROM table_name)

练习题

查询有一门课程授课开课时间大于‘Eastern Heretic’教师任意一门开课时间的教师姓名