CppGuide社区
目录

第9章 子查询

# 第9章 子查询

子查询是一种强大的工具，可用于SQL的所有四种数据操作语句中。本章将详细探讨子查询的众多用法。

# 什么是子查询？

子查询是包含在另一条SQL语句（在后续讨论中，我将其称为主查询语句）中的查询。子查询始终用括号括起来，并且通常在主查询语句之前执行。与任何查询一样，子查询返回的结果集可能包含：

  • 单行单列
  • 多行单列
  • 多行多列

子查询返回的结果集类型决定了它的使用方式，以及主查询语句可以使用哪些运算符来处理子查询返回的数据。当主查询语句执行完毕后，子查询返回的数据会被丢弃，这使得子查询就像一个具有语句作用域的临时表（也就是说，在SQL语句执行完毕后，服务器会释放分配给子查询结果的所有内存）。

在前面的章节中，你已经见过几个子查询的示例，下面是一个简单的示例：

mysql> SELECT account_id, product_cd, cust_id, avail_balance
    -> FROM account
    -> WHERE account_id = (SELECT MAX(account_id) FROM account);
1
2
3
+------------+------------+----------+-----------------+
| account_id | product_cd | cust_id  | avail_balance   |
+------------+------------+----------+-----------------+
|         29 | SBL        |       13 |        50000.00 |
+------------+------------+----------+-----------------+
1 row in set (0.65 sec)
1
2
3
4
5
6

在这个示例中，子查询返回account表中account_id列的最大值，主查询语句随后返回该账户的相关数据。如果你对某个子查询的作用感到困惑，可以单独运行该子查询（去掉括号），查看它返回的内容。下面是前面示例中的子查询：

mysql> SELECT MAX(account_id) FROM account;
1
+------------------+
| MAX(account_id)  |
+------------------+
|               29 |
+------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6

因此，该子查询返回单行单列，这使得它可以作为相等条件中的一个表达式来使用（如果子查询返回两行或更多行，它可以与某些内容进行比较，但不能等于任何内容，后面会详细介绍）。在这种情况下，你可以获取子查询返回的值，并将其代入主查询中筛选条件的右侧表达式，如下所示：

mysql> SELECT account_id, product_cd, cust_id, avail_balance
    -> FROM account
    -> WHERE account_id = 29;
1
2
3
+------------+------------+----------+-----------------+
| account_id | product_cd | cust_id  | avail_balance   |
+------------+------------+----------+-----------------+
|         29 | SBL        |       13 |        50000.00 |
+------------+------------+----------+-----------------+
1 row in set (0.02 sec)
1
2
3
4
5
6

在这种情况下，子查询很有用，因为它允许你在单个查询中检索编号最大的账户的信息，而无需先使用一个查询获取最大的account_id，再编写第二个查询从account表中检索所需的数据。正如你将看到的，子查询在许多其他情况下也很有用，并且可能成为你SQL工具库中最强大的工具之一。

# 子查询类型

除了前面提到的子查询返回的结果集类型（单行/单列、单行/多列或多列）的差异之外，还可以通过另一个因素来区分子查询：有些子查询是完全独立的（称为非关联子查询），而有些则会引用主查询语句中的列（称为关联子查询）。接下来的几个部分将探讨这两种子查询类型，并展示可以用于处理它们的不同运算符。

# 非关联子查询

本章前面的示例是一个非关联子查询；它可以单独执行，并且不引用主查询语句中的任何内容。你遇到的大多数子查询都属于这种类型，除非你编写的是更新或删除语句，这些语句经常会使用关联子查询（后面会详细介绍）。除了是非关联子查询之外，本章前面的示例还返回一个包含单行单列的表。这种类型的子查询被称为标量子查询，可以使用常规运算符（=、<>、<、>、<=、>=）出现在条件的任意一侧。下面的示例展示了如何在不等式条件中使用标量子查询：

mysql> SELECT account_id,
    ->        product_cd,
    ->        cust_id,
    ->        avail_balance
    -> FROM account
    -> WHERE open_emp_id <> (
        SELECT e.emp_id
        FROM employee e
        INNER JOIN branch b
        ON e.assigned_branch_id = b.branch_id
        WHERE e.title = 'Head Teller'
          AND b.city = 'Woburn'
    );
+------------+------------+---------+---------------+
| account_id | product_cd | cust_id | avail_balance |
+------------+------------+---------+---------------+
| 7          | CHK        | 3       | 1057.75       |
| 8          | MM         | 3       | 2212.50       |
| 10         | CHK        | 4       | 534.12        |
| 11         | SAV        | 4       | 767.77        |
| 12         | MM         | 4       | 5487.09       |
| 13         | CHK        | 5       | 2237.97       |
| 14         | CHK        | 6       | 122.37        |
| 15         | CD         | 6       | 10000.00      |
| 18         | CHK        | 8       | 3487.19       |
| 19         | SAV        | 8       | 387.99        |
| 21         | CHK        | 9       | 125.67        |
| 22         | MM         | 9       | 9345.55       |
| 23         | CD         | 9       | 1500.00       |
| 24         | CHK        | 10      | 23575.12      |
| 25         | BUS        | 10      | 0.00          |
| 28         | CHK        | 12      | 38552.05      |
| 29         | SBL        | 13      | 50000.00      |
+------------+------------+---------+---------------+
17 rows in set (0.86 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

这个查询返回所有不是由沃本（Woburn）分行的首席出纳员开设的账户的数据（编写该子查询时假设每个分行只有一位首席出纳员）。这个示例中的子查询比前面的示例稍微复杂一些，它连接了两个表并包含两个筛选条件。子查询可以根据需要简单或复杂，并且可以使用所有可用的查询子句（select、from、where、group by、having和order by）。

如果你在相等条件中使用子查询，但子查询返回多行数据，将会收到一个错误。例如，如果你修改前面的查询，使子查询返回沃本分行的所有出纳员，而不是单个首席出纳员，将会收到以下错误：

mysql> SELECT account_id, product_cd, cust_id, avail_balance
    -> FROM account
    -> WHERE open_emp_id <> (SELECT e.emp_id
    ->       FROM employee e INNER JOIN branch b
    ->          ON e.assigned_branch_id = b.branch_id
    ->      WHERE e.title = 'Teller' AND b.city = 'Woburn');
ERROR 1242 (21000): Subquery returns more than 1 row
1
2
3
4
5
6
7

如果你单独运行该子查询，将会看到以下结果：

mysql> SELECT e.emp_id
    -> FROM employee e INNER JOIN branch b
    ->     ON e.assigned_branch_id = b.branch_id
    -> WHERE e.title = 'Teller' AND b.city = 'Woburn';
+--------+
| emp_id |
+--------+
|     11 |
|     12 |
+--------+
2 rows in set (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11

主查询失败是因为一个表达式（open_emp_id）不能与一组表达式（员工ID 11和12）进行相等比较。换句话说，单个值不能等于一组值。在下一节中，你将看到如何通过使用不同的运算符来解决这个问题。

# 多行单列子查询

如果你的子查询返回多行数据，就不能像前面的示例那样在相等条件的一侧使用它。不过，有另外四个运算符可以用于构建针对这类子查询的条件。

# in和not in运算符

虽然不能将单个值与一组值进行相等比较，但可以检查单个值是否存在于一组值中。下面的示例虽然没有使用子查询，但展示了如何使用in运算符构建一个条件，在一组值中搜索某个值：

mysql> SELECT branch_id, name, city
    -> FROM branch
    -> WHERE name IN ('Headquarters', 'Quincy Branch');
+-----------+----------------+----------+
| branch_id | name           | city     |
+-----------+----------------+----------+
|         1 | Headquarters   | Waltham  |
|         3 | Quincy Branch  | Quincy   |
+-----------+----------------+----------+
2 rows in set (0.03 sec)
1
2
3
4
5
6
7
8
9
10

条件左侧的表达式是name列，而右侧是一组字符串。in运算符检查这两个字符串中的任何一个是否能在name列中找到；如果能找到，则满足条件，该行将被添加到结果集中。你也可以使用两个相等条件来实现相同的结果，如下所示：

mysql> SELECT branch_id, name, city
    -> FROM branch
    -> WHERE name = 'Headquarters' OR name = 'Quincy Branch';
+-----------+----------------+----------+
| branch_id | name           | city     |
+-----------+----------------+----------+
|         1 | Headquarters   | Waltham  |
|         3 | Quincy Branch  | Quincy   |
+-----------+----------------+----------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10

当这组值只包含两个表达式时，这种方法似乎还合理，但很容易看出，如果这组值包含几十个（或几百个、几千个等）值，使用单个in运算符的条件会更可取。

虽然偶尔会创建一组字符串、日期或数字用于条件的一侧，但更常见的情况是在查询执行时通过返回一行或多行数据的子查询来生成这组值。下面的查询在筛选条件的右侧使用in运算符和子查询，以找出哪些员工管理其他员工：

mysql> SELECT emp_id, fname, lname, title
    -> FROM employee
    -> WHERE emp_id IN (SELECT superior_emp_id
    ->       FROM employee);
+--------+----------+-----------+----------------------------------+
| emp_id | fname    | lname     | title                            |
+--------+----------+-----------+----------------------------------+
|      1 | Michael  | Smith     | President                        |
|      3 | Robert   | Tyler     | Treasurer                        |
|      4 | Susan    | Hawthorne | Operations Manager               |
|      6 | Helen    | Fleming   | Head Teller                      |
|     10 | Paula    | Roberts   | Head Teller                      |
|     13 | John     | Blake     | Head Teller                      |
|     16 | Theresa  | Markham   | Head Teller                      |
+--------+----------+-----------+----------------------------------+
7 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

子查询返回所有管理其他员工的员工ID，主查询从employee表中检索这些员工的四列数据。下面是子查询的结果：

mysql> SELECT superior_emp_id
    -> FROM employee;
+-------------------+
| superior_emp_id   |
+-------------------+
| NULL              |
| 1                 |
| 1                 |
| 3                 |
| 4                 |
| 4                 |
| 4                 |
| 4                 |
| 4                 |
| 6                 |
| 6                 |
| 6                 |
| 10                |
| 10                |
| 13                |
| 13                |
| 16                |
| 16                |
+-------------------+
18 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

如你所见，有些员工ID出现了多次，因为有些员工管理多个人。这不会对主查询的结果产生不利影响，因为某个员工ID在子查询结果集中出现一次还是多次并不重要。当然，如果子查询返回的表中存在重复项让你感到困扰，可以在子查询的select子句中添加distinct关键字，但这不会改变主查询的结果集。

除了查看某个值是否存在于一组值中，你还可以使用not in运算符来检查相反的情况。下面是使用not in而非in重写的上一个查询：

mysql> SELECT emp_id, fname, lname, title
    -> FROM employee
    -> WHERE emp_id NOT IN
    ->     (SELECT superior_emp_id
    ->      FROM employee
    ->      WHERE superior_emp_id IS NOT NULL);
+--------+----------+----------+------------------+
| emp_id | fname    | lname    | title            |
+--------+----------+----------+------------------+
|      2 | Susan    | Barker   | Vice President   |
|      5 | John     | Gooding  | Loan Manager     |
|      7 | Chris    | Tucker   | Teller           |
|      8 | Sarah    | Parker   | Teller           |
|      9 | Jane     | Grossman | Teller           |
|     11 | Thomas   | Ziegler  | Teller           |
|     12 | Samantha | Jameson  | Teller           |
|     14 | Cindy    | Mason    | Teller           |
|     15 | Frank    | Portman  | Teller           |
|     17 | Beth     | Fowler   | Teller           |
|     18 | Rick     | Tulman   | Teller           |
+--------+----------+----------+------------------+
11 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

此查询查找所有不管理他人的员工。在这个查询中，我需要给子查询添加一个过滤条件，以确保子查询返回的表中不出现空值（NULL）；关于为什么在这种情况下需要这个过滤条件，请看下一节的解释。

# all运算符

in运算符用于查看某个表达式是否能在一组表达式中找到，而all运算符允许你在单个值和一组值中的每个值之间进行比较。要构建这样的条件，你需要将比较运算符（=、<?>、<、>等）与all运算符结合使用。例如，下一个查询查找所有员工ID不等于任何主管员工ID的员工：

mysql> SELECT emp_id, fname, lname, title
    -> FROM employee
    -> WHERE emp_id <> ALL
    ->     (SELECT superior_emp_id
    ->      FROM employee
    ->      WHERE superior_emp_id IS NOT NULL);
+--------+----------+----------+------------------+
| emp_id | fname    | lname    | title            |
+--------+----------+----------+------------------+
|      2 | Susan    | Barker   | Vice President   |
|      5 | John     | Gooding  | Loan Manager     |
|      7 | Chris    | Tucker   | Teller           |
|      8 | Sarah    | Parker   | Teller           |
|      9 | Jane     | Grossman | Teller           |
|     11 | Thomas   | Ziegler  | Teller           |
|     12 | Samantha | Jameson  | Teller           |
|     14 | Cindy    | Mason    | Teller           |
|     15 | Frank    | Portman  | Teller           |
|     17 | Beth     | Fowler   | Teller           |
|     18 | Rick     | Tulman   | Teller           |
+--------+----------+----------+------------------+
11 rows in set (0.05 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

同样，子查询返回管理他人的员工的ID集合，而主查询返回ID不等于子查询返回的所有ID的每个员工的数据。换句话说，该查询查找所有不是主管的员工。如果你觉得这种方法有点笨拙，那并不奇怪；大多数人更倾向于用不同的方式表述查询，避免使用all运算符。例如，这个查询生成的结果与上一节中使用not in运算符的最后一个示例相同。这只是个人偏好问题，但我认为大多数人会觉得使用not in的版本更容易理解。

在使用not in或<> all将一个值与一组值进行比较时，你必须注意确保这组值中不包含空值（NULL），因为服务器会将表达式左侧的值与集合中的每个成员进行比较，而任何将值与空值进行比较的尝试都会得到未知结果。因此，以下查询返回一个空集：

mysql> SELECT emp_id, fname, lname, title
    -> FROM employee
    -> WHERE emp_id NOT IN (1, 2, NULL);
Empty set (0.00 sec)
1
2
3
4

在某些情况下，all运算符的使用会更自然一些。下一个示例使用all查找可用余额小于弗兰克·塔克（Frank Tucker）所有账户的账户：

mysql> SELECT account_id, cust_id, product_cd, avail_balance
    -> FROM account
    -> WHERE avail_balance < ALL
    ->     (SELECT a.avail_balance
    ->      FROM account a
    ->      INNER JOIN individual i
    ->      ON a.cust_id = i.cust_id
    ->      WHERE i.fname = 'Frank' AND i.lname = 'Tucker');
+-------------+---------+------------+---------------+
| account_id  | cust_id | product_cd | avail_balance |
+-------------+---------+------------+---------------+
|           2 |       1 | SAV        |         500.00 |
|           5 |       2 | SAV        |         200.00 |
|          10 |       4 | CHK        |         534.12 |
|          11 |       4 | SAV        |         767.77 |
|          14 |       6 | CHK        |         122.37 |
|          19 |       8 | SAV        |         387.99 |
|          21 |       9 | CHK        |         125.67 |
|          25 |      10 | BUS        |           0.00 |
+-------------+---------+------------+---------------+
8 rows in set (0.17 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

下面是子查询返回的数据，它包含弗兰克每个账户的可用余额：

mysql> SELECT a.avail_balance
    -> FROM account a
    -> INNER JOIN individual i
    -> ON a.cust_id = i.cust_id
    -> WHERE i.fname = 'Frank' AND i.lname = 'Tucker';
+---------------+
| avail_balance |
+---------------+
|        1057.75 |
|        2212.50 |
+---------------+
2 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12

弗兰克有两个账户，最低余额为1057.75美元。主查询查找所有余额小于弗兰克任何一个账户余额的账户，所以结果集包括所有余额小于1057.75美元的账户。

# any运算符

与all运算符类似，any运算符允许将一个值与一组值中的成员进行比较；然而，与all不同的是，使用any运算符的条件只要有一次比较结果为真，整个条件就为真。这与前面使用all运算符的示例不同，使用all运算符的条件只有在与集合中所有成员的比较结果都为真时才为真。例如，你可能想要查找所有可用余额大于弗兰克·塔克任何一个账户余额的账户：

mysql> SELECT account_id, cust_id, product_cd, avail_balance
    -> FROM account
    -> WHERE avail_balance > ANY
    ->     (SELECT a.avail_balance
    ->      FROM account a
    ->      INNER JOIN individual i
    ->      ON a.cust_id = i.cust_id
    ->      WHERE i.fname = 'Frank' AND i.lname = 'Tucker');
+-------------+---------+------------+---------------+
| account_id  | cust_id | product_cd | avail_balance |
+-------------+---------+------------+---------------+
|           3 |       1 | CD         |        3000.00 |
|           4 |       2 | CHK        |        2258.02 |
|           8 |       3 | MM         |        2212.50 |
|          12 |       4 | MM         |        5487.09 |
|          13 |       5 | CHK        |        2237.97 |
|          15 |       6 | CD         |       10000.00 |
|          17 |       7 | CD         |        5000.00 |
|          18 |       8 | CHK        |        3487.19 |
|          22 |       9 | MM         |        9345.55 |
|          23 |       9 | CD         |        1500.00 |
|          24 |      10 | CHK        |       23575.12 |
|          27 |      11 | BUS        |        9345.55 |
|          28 |      12 | CHK        |       38552.05 |
|          29 |      13 | SBL        |       50000.00 |
+-------------+---------+------------+---------------+
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

弗兰克有两个账户，余额分别为1057.75美元和2212.50美元；要使一个账户的余额大于这两个账户中的任何一个，该账户的余额至少必须为1057.75美元。

虽然大多数人更喜欢使用in，但使用= any与使用in运算符是等效的。

# 多列子查询

到目前为止，本章中的所有子查询示例都只返回了单个列和一行或多行数据。然而，在某些情况下，你可以使用返回两列或更多列的子查询。为了展示多列子查询的用处，先来看一个使用多个单列子查询的示例可能会有所帮助：

mysql> SELECT account_id, product_cd, cust_id
    -> FROM account
    -> WHERE open_branch_id =
    ->     (SELECT branch_id
    ->      FROM branch
    ->      WHERE name = 'Woburn Branch')
    -> AND open_emp_id IN
    ->     (SELECT emp_id
    ->      FROM employee
    ->      WHERE title = 'Teller' OR title = 'Head Teller');
+-------------+------------+---------+
| account_id  | product_cd | cust_id |
+-------------+------------+---------+
|           1 | CHK        |       1 |
|           2 | SAV        |       1 |
|           3 | CD         |       1 |
|           4 | CHK        |       2 |
|           5 | SAV        |       2 |
|          17 | CD         |       7 |
|          27 | BUS        |      11 |
+-------------+------------+---------+
7 rows in set (0.09 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

此查询使用两个子查询来确定沃本分行（Woburn Branch）的ID和所有银行出纳员的ID，然后主查询使用这些信息来检索沃本分行出纳员开设的所有支票账户。不过，由于员工表包含了每个员工所属分行的信息，你可以通过将account.open_branch_id和account.open_emp_id列与针对员工表和分行表的单个子查询进行比较，来获得相同的结果。要做到这一点，你的过滤条件必须用括号括起account表中的两列，并且顺序要与子查询返回的顺序相同，如下所示：

mysql> SELECT account_id, product_cd, cust_id
    -> FROM account
    -> WHERE (open_branch_id, open_emp_id) IN
    ->     (SELECT b.branch_id, e.emp_id
    ->      FROM branch b
    ->      INNER JOIN employee e
    ->      ON b.branch_id = e.assigned_branch_id
    ->      WHERE b.name = 'Woburn Branch'
    ->      AND (e.title = 'Teller' OR e.title = 'Head Teller'));
+-------------+------------+---------+
| account_id  | product_cd | cust_id |
+-------------+------------+---------+
|           1 | CHK        |       1 |
|           2 | SAV        |       1 |
|           3 | CD         |       1 |
|           4 | CHK        |       2 |
|           5 | SAV        |       2 |
|          17 | CD         |       7 |
|          27 | BUS        |      11 |
+-------------+------------+---------+
7 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

这个版本的查询与前面的示例执行相同的功能，但使用了一个返回两列的单个子查询，而不是两个分别返回单列的子查询。

当然，你可以简单地重写前面的示例，通过连接三个表来代替使用子查询，但在学习SQL时，了解实现相同结果的多种方法是很有帮助的。不过，下面还有一个需要使用子查询的示例。假设客户对account表中可用余额/待处理余额列中的错误值提出了一些投诉。你的任务是找出所有余额与该账户交易金额总和不匹配的账户。以下是这个问题的部分解决方案：

SELECT 'ALERT!  :  Account  #1  Has  Incorrect  Balance!' FROM account
WHERE (avail_balance,  pending_balance)  <>
(SELECT  SUM(<expression  to  generate  available  balance>), SUM(<expression  to  generate  pending  balance>)
FROM  transaction
WHERE  account_id  =  1) AND  account_id  =  1;
1
2
3
4
5

如你所见，我还没有填写用于计算可用余额和待处理余额的交易金额总和的表达式，但我保证在第11章你学习了如何构建case表达式后完成这项工作。即便如此，这个查询已经足够完整，能够看出子查询从transaction表中生成了两个总和，然后将其与account表中的avail_balance和pending_balance列进行比较。子查询和主查询都包含过滤条件account_id = 1，所以目前这个形式的查询一次只能检查一个账户。在下一节中，你将学习如何编写一个更通用的查询形式，以便一次执行就能检查所有账户。

# 相关子查询

到目前为止展示的所有子查询都独立于它们的主查询语句，这意味着你可以单独执行它们并查看结果。另一方面，相关子查询依赖于它的主查询语句，它会从主查询语句中引用一个或多个列。与非相关子查询不同，相关子查询在主查询语句执行之前不会先执行一次；相反，相关子查询会为每个候选行（可能包含在最终结果中的行）执行一次。例如，以下查询使用相关子查询来计算每个客户的账户数量，然后主查询检索拥有恰好两个账户的客户：

mysql> SELECT c.cust_id, c.cust_type_cd, c.city
    -> FROM customer c
    -> WHERE 2 =
    ->     (SELECT COUNT(*)
    ->      FROM account a
    ->      WHERE a.cust_id = c.cust_id);
+---------+---------------+----------+
| cust_id | cust_type_cd  | city     |
+---------+---------------+----------+
|       2 | I             | Woburn   |
|       3 | I             | Quincy   |
|       6 | I             | Waltham  |
|       8 | I             | Salem    |
|      10 | B             | Salem    |
+---------+---------------+----------+
5 rows in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

子查询末尾对c.cust_id的引用使该子查询成为相关子查询；主查询必须为c.cust_id提供值，子查询才能执行。在这种情况下，主查询从customer表中检索所有13行数据，并为每个客户执行一次子查询，每次执行时传入相应的客户ID。如果子查询返回的值为2，则满足过滤条件，该行将被添加到结果集中。

除了相等条件，你还可以在其他类型的条件中使用相关子查询，如下所示的范围条件：

mysql> SELECT c.cust_id, c.cust_type_cd, c.city
    -> FROM customer c
    -> WHERE (SELECT SUM(a.avail_balance)
    ->         FROM account a
    ->         WHERE a.cust_id = c.cust_id)
    ->     BETWEEN 5000 AND 10000;
+---------+---------------+-----------------+
| cust_id | cust_type_cd  | city            |
+---------+---------------+-----------------+
|       4 | I             | Waltham         |
|       7 | I             | Wilmington      |
|      11 | B             | Wilmington      |
+---------+---------------+-----------------+
3 rows in set (0.02 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

这个查询是对上一个查询的变体，它查找所有账户总可用余额在5000美元到10000美元之间的客户。同样，相关子查询会执行13次（每个客户行执行一次），每次执行子查询都会返回给定客户的账户总余额。

上一个查询还有一个细微的差别，即子查询位于条件的左侧，这可能看起来有点奇怪，但完全是有效的。

在上一节的结尾，我展示了如何根据账户的交易记录检查账户的可用余额和待处理余额，并承诺向你展示如何修改示例以便一次执行就能检查所有账户。以下是再次展示的示例：

SELECT 'ALERT!  :  Account  #1  Has  Incorrect  Balance!' FROM account
WHERE (avail_balance,  pending_balance)  <>
(SELECT  SUM(<expression  to  generate  available  balance>), SUM(<expression  to  generate  pending  balance>)
FROM  transaction
WHERE  account_id  =  1) AND  account_id  =  1;
1
2
3
4
5

使用相关子查询而非非相关子查询，你可以执行一次主查询，子查询会为每个账户运行一次。以下是更新后的版本：

SELECT  CONCAT('ALERT!  :  Account  #',  a.account_id, '   Has  Incorrect  Balance!')
FROM  account  a
WHERE  (a.avail_balance,  a.pending_balance)  <>
(SELECT  SUM(<expression  to  generate  available  balance>), SUM(<expression  to  generate  pending  balance>)
FROM  transaction  t
WHERE t.account_id  =  a.account_id);
1
2
3
4
5
6

现在，子查询包含一个过滤条件，将交易的账户ID与主查询中的账户ID关联起来。选择子句也进行了修改，将包含账户ID的警报消息连接起来，而不是使用硬编码值1。

# EXISTS操作符

虽然你经常会看到相关子查询（correlated subqueries）用于不等式和范围条件，但构建利用相关子查询的条件时，最常用的操作符是EXISTS操作符。当你想要确认某种关系存在，而不考虑数量时，就可以使用EXISTS操作符。例如，以下查询会找出在特定日期有交易记录的所有账户，而不关心交易的数量：

SELECT  a.account_id,  a.product_cd,  a.cust_id,  a.avail_balance 
FROM  account  a
WHERE  EXISTS  (SELECT  1 
                FROM  transaction  t
                WHERE  t.account_id  =  a.account_id 
                AND  t.txn_date  =  '2008-09-22');
1
2
3
4
5
6

使用EXISTS操作符时，子查询可以返回零行、一行或多行数据，该条件只是检查子查询是否返回了任何行。查看子查询的SELECT子句，你会发现它只包含一个字面值（1）；由于主查询中的条件只需要知道返回了多少行，所以子查询实际返回的数据并不重要。子查询可以返回任何你想要的数据，如下所示：

SELECT  a.account_id,  a.product_cd,  a.cust_id,  a.avail_balance 
FROM  account  a
WHERE  EXISTS   (SELECT t.txn_id,  'hello',  3.1415927  
                FROM  transaction  t
                WHERE  t.account_id  =  a.account_id 
                AND  t.txn_date  =  '2008-09-22');
1
2
3
4
5
6

不过，惯例是在使用EXISTS时，指定SELECT 1或SELECT *。

你也可以使用NOT EXISTS来检查子查询是否没有返回任何行，如下所示：

mysql> SELECT a.account_id,
    ->        a.product_cd,
    ->        a.cust_id
    -> FROM account a
    -> WHERE NOT EXISTS (
        SELECT 1
        FROM business b
        WHERE b.cust_id = a.cust_id
    );
+------------+------------+---------+
| account_id | product_cd | cust_id |
+------------+------------+---------+
| 1          | CHK        | 1       |
| 2          | SAV        | 1       |
| 3          | CD         | 1       |
| 4          | CHK        | 2       |
| 5          | SAV        | 2       |
| 7          | CHK        | 3       |
| 8          | MM         | 3       |
| 10         | CHK        | 4       |
| 11         | SAV        | 4       |
| 12         | MM         | 4       |
| 13         | CHK        | 5       |
| 14         | CHK        | 6       |
| 15         | CD         | 6       |
| 17         | CD         | 7       |
| 18         | CHK        | 8       |
| 19         | SAV        | 8       |
| 21         | CHK        | 9       |
| 22         | MM         | 9       |
| 23         | CD         | 9       |
+------------+------------+---------+
19 rows in set (0.99 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

此查询会找出客户ID未出现在business表中的所有客户，这是一种间接找出所有非企业客户的方法。

# 使用相关子查询进行数据操作

本章到目前为止的所有示例都是SELECT语句，但不要认为这意味着子查询在其他SQL语句中没有用处。子查询在UPDATE、DELETE和INSERT语句中也大量使用，相关子查询在UPDATE和DELETE语句中经常出现。以下是一个使用相关子查询修改account表中last_activity_date列的示例：

UPDATE  account  a
SET  a.last_activity_date  = (SELECT  MAX(t.txn_date)
                              FROM  transaction  t
                              WHERE  t.account_id  =  a.account_id);
1
2
3
4

该语句（因为没有WHERE子句）会通过查找每个账户的最新交易日期来修改account表中的每一行数据。虽然期望每个账户至少有一笔关联交易似乎是合理的，但在尝试更新last_activity_date列之前，最好先检查一下该账户是否有任何交易；否则，该列将被设置为NULL，因为子查询不会返回任何行。以下是更新语句的另一个版本，这次在WHERE子句中使用了第二个相关子查询：

UPDATE  account  a
SET  a.last_activity_date  = (SELECT  MAX(t.txn_date)
                              FROM  transaction  t
                              WHERE  t.account_id  =  a.account_id) 
WHERE  EXISTS   (SELECT  1
                 FROM  transaction  t
                 WHERE  t.account_id  =  a.account_id);
1
2
3
4
5
6
7

这两个相关子查询除了SELECT子句外完全相同。不过，SET子句中的子查询只有在UPDATE语句的WHERE子句条件求值为TRUE时（意味着为该账户找到了至少一笔交易）才会执行，从而保护last_activity_date列中的数据不会被NULL覆盖。

相关子查询在DELETE语句中也很常见。例如，你可能会在每个月末运行一个数据维护脚本，删除不必要的数据。该脚本可能包含以下语句，用于从department表中删除在employee表中没有子行的数据：

DELETE  FROM  department
WHERE  NOT  EXISTS   (SELECT   1 
                      FROM  employee
                      WHERE  employee.dept_id  =  department.dept_id);
1
2
3
4

在MySQL中使用相关子查询与DELETE语句时，请记住，出于某种原因，使用DELETE时不允许使用表别名，这就是为什么我在子查询中必须使用完整的表名。而在大多数其他数据库服务器中，你可以为department表和employee表提供别名，如下所示：

DELETE  FROM  department  d    
WHERE  NOT  EXISTS   (SELECT   1
                      FROM  employee  e
                      WHERE  e.dept_id  =  d.dept_id);
1
2
3
4

# 何时使用子查询

既然你已经了解了不同类型的子查询，以及可以用于处理子查询返回数据的不同操作符，那么现在是时候探索如何使用子查询构建强大的SQL语句了。接下来的三个部分将展示如何使用子查询构建自定义表、构建条件以及在结果集中生成列值。

# 子查询作为数据源

在第3章中，我提到SELECT语句的FROM子句用于指定查询要使用的表。由于子查询会生成包含数据行和列的结果集，所以在FROM子句中与表一起包含子查询是完全有效的。虽然乍一看，这似乎是一个有趣但没有太多实际价值的功能，但在编写查询时，将子查询与表一起使用是最强大的工具之一。以下是一个简单的示例：

mysql> SELECT d.dept_id,
    ->        d.name,
    ->        e_cnt.how_many num_employees
    -> FROM department d
    -> INNER JOIN (
        SELECT dept_id,
               COUNT(*) how_many
        FROM employee
        GROUP BY dept_id
    ) e_cnt
    -> ON d.dept_id = e_cnt.dept_id;
+---------+----------------+---------------+
| dept_id | name           | num_employees |
+---------+----------------+---------------+
| 1       | Operations     | 14            |
| 2       | Loans          | 1             |
| 3       | Administration | 3             |
+---------+----------------+---------------+
3 rows in set (0.04 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

在这个例子中，一个子查询生成了一个部门ID列表以及分配给每个部门的员工数量。以下是该子查询生成的结果集：

mysql> SELECT dept_id,
    ->        COUNT(*) how_many
    -> FROM employee
    -> GROUP BY dept_id;
+---------+----------+
| dept_id | how_many |
+---------+----------+
| 1       | 14       |
| 2       | 1        |
| 3       | 3        |
+---------+----------+
3 rows in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12

该子查询被命名为e_cnt，并通过dept_id列与department表进行连接。主查询随后从department表中检索部门ID和名称，以及从e_cnt子查询中检索员工数量。

在FROM子句中使用的子查询必须是非相关的；它们首先被执行，数据会被保存在内存中，直到主查询执行完毕。在编写查询时，子查询提供了极大的灵活性，因为你可以超越现有表的集合，创建几乎任何你想要的数据视图，然后将结果与其他表或子查询进行连接。如果你正在编写报告或生成外部系统的数据馈送，你可能能够用单个查询完成过去需要多个查询或使用过程语言才能完成的事情。

# 数据虚构

除了使用子查询汇总现有数据外，你还可以使用子查询生成数据库中不存在的任何形式的数据。例如，你可能希望根据客户在存款账户中的金额对客户进行分组，但你想要使用数据库中未存储的分组定义。假设你想将客户分类到表9 - 1所示的组中。 表9 - 1. 客户余额分组

组名 下限 上限
小客户（Small Fry） 0 $4,999.99
普通客户（Average Joes） $5,000 $9,999.99
大客户（Heavy Hitters） $10,000 $9,999,999.99

要在单个查询中生成这些组，你需要一种定义这三个组的方法。第一步是定义一个生成组定义的查询：

mysql> SELECT 'Small Fry' name,
    ->        0 low_limit,
    ->        4999.99 high_limit
    -> UNION ALL
    -> SELECT 'Average Joes' name,
    ->        5000 low_limit,
    ->        9999.99 high_limit
    -> UNION ALL
    -> SELECT 'Heavy Hitters' name,
    ->        10000 low_limit,
    ->        9999999.99 high_limit;
+---------------+-----------+------------+
| name          | low_limit | high_limit |
+---------------+-----------+------------+
| Small Fry     | 0         | 4999.99    |
| Average Joes  | 5000      | 9999.99    |
| Heavy Hitters | 10000     | 9999999.99 |
+---------------+-----------+------------+
3 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

我使用了集合操作符UNION ALL将三个单独查询的结果合并成一个结果集。每个查询检索三个字面值，这三个查询的结果组合在一起，生成了一个包含三行三列的结果集。现在你有了一个生成所需分组的查询，你可以将它放在另一个查询的FROM子句中，以生成客户分组：

mysql> SELECT groups.name,
    ->        COUNT(*) num_customers
    -> FROM (
        SELECT SUM(a.avail_balance) cust_balance
        FROM account a
        INNER JOIN product p
        ON a.product_cd = p.product_cd
        WHERE p.product_type_cd = 'ACCOUNT'
        GROUP BY a.cust_id
    ) cust_rollup
    -> INNER JOIN (
        SELECT 'Small Fry' name,
               0 low_limit,
               4999.99 high_limit
        UNION ALL
        SELECT 'Average Joes' name,
               5000 low_limit,
               9999.99 high_limit
        UNION ALL
        SELECT 'Heavy Hitters' name,
               10000 low_limit,
               9999999.99 high_limit
    ) groups
    -> ON cust_rollup.cust_balance BETWEEN groups.low_limit AND groups.high_limit
    -> GROUP BY groups.name;
+---------------+---------------+
| name          | num_customers |
+---------------+---------------+
| Average Joes  | 2             |
| Heavy Hitters | 4             |
| Small Fry     | 5             |
+---------------+---------------+
3 rows in set (0.01 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

FROM子句包含两个子查询；第一个子查询名为cust_rollup，返回每个客户的总存款余额，而第二个子查询名为groups，生成三个客户分组。以下是cust_rollup生成的数据：

mysql> SELECT SUM(a.avail_balance) cust_balance
    -> FROM account a
    -> INNER JOIN product p
    -> ON a.product_cd = p.product_cd
    -> WHERE p.product_type_cd = 'ACCOUNT'
    -> GROUP BY a.cust_id;
+--------------+
| cust_balance |
+--------------+
| 4557.75      |
| 2458.02      |
| 3270.25      |
| 6788.98      |
| 2237.97      |
| 10122.37     |
| 5000.00      |
| 3875.18      |
| 10971.22     |
| 23575.12     |
| 38552.05     |
+--------------+
11 rows in set (0.05 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

cust_rollup生成的数据随后通过一个范围条件（cust_rollup.cust_balance BETWEEN groups.low_limit AND groups.high_limit）与groups表进行连接。最后，对连接后的数据进行分组，并统计每个分组中的客户数量，以生成最终的结果集。

当然，你也可以选择构建一个永久性的表来存储分组定义，而不是使用子查询。采用这种方法，过一段时间后你会发现数据库中充斥着各种小的专用表，而且你可能都不记得创建它们的原因了。我曾在允许数据库用户为特定目的创建自己的表的环境中工作过，结果很糟糕（表未被纳入备份、服务器升级时丢失表、因空间分配问题导致服务器停机等等）。然而，有了子查询，你就能够遵循一种策略，即只有在有明确的业务需求需要存储新数据时，才向数据库中添加表。

# 面向任务的子查询

在用于报告或数据馈送生成的系统中，你经常会遇到如下这样的查询：

mysql> SELECT p.name product,
    ->        b.name branch,
    ->        CONCAT(e.fname, ' ', e.lname) name,
    ->        SUM(a.avail_balance) tot_deposits
    -> FROM account a
    -> INNER JOIN employee e
    -> ON a.open_emp_id = e.emp_id
    -> INNER JOIN branch b
    -> ON a.open_branch_id = b.branch_id
    -> INNER JOIN product p
    -> ON a.product_cd = p.product_cd
    -> WHERE p.product_type_cd = 'ACCOUNT'
    -> GROUP BY p.name, b.name, e.fname, e.lname
    -> ORDER BY 1, 2;
+------------------------+---------------+-----------------+--------------+
| product                | branch        | name            | tot_deposits |
+------------------------+---------------+-----------------+--------------+
| certificate of deposit | Headquarters  | Michael Smith   | 11500.00     |
| certificate of deposit | Woburn Branch | Paula Roberts   | 8000.00      |
| checking account       | Headquarters  | Michael Smith   | 782.16       |
| checking account       | Quincy Branch | John Blake      | 1057.75      |
| checking account       | So. NH Branch | Theresa Markham | 67852.33     |
| checking account       | Woburn Branch | Paula Roberts   | 3315.77      |
| money market account   | Headquarters  | Michael Smith   | 14832.64     |
| money market account   | Quincy Branch | John Blake      | 2212.50      |
| savings account        | Headquarters  | Michael Smith   | 767.77       |
| savings account        | So. NH Branch | Theresa Markham | 387.99       |
| savings account        | Woburn Branch | Paula Roberts   | 700.00       |
+------------------------+---------------+-----------------+--------------+
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

这个查询按账户类型、开户员工以及开户分行对所有存款账户余额进行求和。仔细查看这个查询，你会发现product表、branch表和employee表仅用于显示目的，而account表包含了进行分组所需的所有信息（product_cd、open_branch_id、open_emp_id和avail_balance）。因此，你可以将分组任务分离到一个子查询中，然后将其他三个表与子查询生成的表进行连接，以达到预期的最终结果。以下是分组子查询：

mysql> SELECT product_cd,
    ->        open_branch_id branch_id,
    ->        open_emp_id emp_id,
    ->        SUM(avail_balance) tot_deposits
    -> FROM account
    -> GROUP BY product_cd, open_branch_id, open_emp_id;
+------------+-----------+--------+--------------+
| product_cd | branch_id | emp_id | tot_deposits |
+------------+-----------+--------+--------------+
| BUS        | 2         | 10     | 9345.55      |
| BUS        | 4         | 16     | 0.00         |
| CD         | 1         | 1      | 11500.00     |
| CD         | 2         | 10     | 8000.00      |
| CHK        | 1         | 1      | 782.16       |
| CHK        | 2         | 10     | 3315.77      |
| CHK        | 3         | 13     | 1057.75      |
| CHK        | 4         | 16     | 67852.33     |
| MM         | 1         | 1      | 14832.64     |
| MM         | 3         | 13     | 2212.50      |
| SAV        | 1         | 1      | 767.77       |
| SAV        | 2         | 10     | 700.00       |
| SAV        | 4         | 16     | 387.99       |
| SBL        | 3         | 13     | 50000.00     |
+------------+-----------+--------+--------------+
14 rows in set (0.02 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

这是该查询的核心部分；其他表只是用于提供有意义的字符串，来替代product_cd、open_branch_id和open_emp_id这些外键列。接下来的查询将针对account表的查询封装在一个子查询中，并将生成的表与其他三个表进行连接：

mysql> SELECT p.name product,
    ->        b.name branch,
    ->        CONCAT(e.fname, ' ', e.lname) name,
    ->        account_groups.tot_deposits
    -> FROM (
        SELECT product_cd,
               open_branch_id branch_id,
               open_emp_id emp_id,
               SUM(avail_balance) tot_deposits
        FROM account
        GROUP BY product_cd, open_branch_id, open_emp_id
    ) account_groups
    -> INNER JOIN employee e
    -> ON e.emp_id = account_groups.emp_id
    -> INNER JOIN branch b
    -> ON b.branch_id = account_groups.branch_id
    -> INNER JOIN product p
    -> ON p.product_cd = account_groups.product_cd
    -> WHERE p.product_type_cd = 'ACCOUNT';
+------------------------+---------------+-----------------+--------------+
| product                | branch        | name            | tot_deposits |
+------------------------+---------------+-----------------+--------------+
| certificate of deposit | Headquarters  | Michael Smith   | 11500.00     |
| certificate of deposit | Woburn Branch | Paula Roberts   | 8000.00      |
| checking account       | Headquarters  | Michael Smith   | 782.16       |
| checking account       | Quincy Branch | John Blake      | 1057.75      |
| checking account       | So. NH Branch | Theresa Markham | 67852.33     |
| checking account       | Woburn Branch | Paula Roberts   | 3315.77      |
| money market account   | Headquarters  | Michael Smith   | 14832.64     |
| money market account   | Quincy Branch | John Blake      | 2212.50      |
| savings account        | Headquarters  | Michael Smith   | 767.77       |
| savings account        | So. NH Branch | Theresa Markham | 387.99       |
| savings account        | Woburn Branch | Paula Roberts   | 700.00       |
+------------------------+---------------+-----------------+--------------+
11 rows in set (0.01 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

我知道美是主观的，但我觉得这个版本的查询比那种冗长、平铺直叙的版本更令人满意。这个版本可能执行得也更快，因为分组操作是在较小的数字型外键列（product_cd、open_branch_id、open_emp_id）上进行的，而不是在可能很长的字符串列（branch.name、product.name、employee.fname、employee.lname）上进行。

# 过滤条件中的子查询

本章的许多示例都将子查询用作过滤条件中的表达式，所以这是子查询的主要用途之一也就不足为奇了。不过，使用子查询的过滤条件并不只出现在where子句中。例如，下面的查询在having子句中使用子查询，以找出开设账户最多的员工：

mysql> SELECT open_emp_id,
    ->        COUNT(*) how_many
    -> FROM account
    -> GROUP BY open_emp_id
    -> HAVING COUNT(*) = (
        SELECT MAX(emp_cnt.how_many)
        FROM (
            SELECT COUNT(*) how_many
            FROM account
            GROUP BY open_emp_id
        ) emp_cnt
    );
+-------------+----------+
| open_emp_id | how_many |
+-------------+----------+
| 1           | 8        |
+-------------+----------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

having子句中的子查询查找出任何员工开设的最大账户数，而外部查询则找出开设了该数量账户的员工。如果有多个员工开设的账户数并列最多，那么该查询将返回多行数据。

# 作为表达式生成器的子查询

在本章的最后一部分，我从开头的内容接着讲：单列、单行的标量子查询。除了在过滤条件中使用，标量子查询还可以在表达式能够出现的任何地方使用，包括查询的select和order by子句以及insert语句的values子句。

在第175页的“面向任务的子查询”中，我向你展示了如何使用子查询将分组机制与查询的其他部分分开。下面是同一个查询的另一个版本，它出于相同目的使用子查询，但方式不同：

mysql> SELECT
    ->     (
        SELECT p.name
        FROM product p
        WHERE p.product_cd = a.product_cd
            AND p.product_type_cd = 'ACCOUNT'
    ) product,
    ->     (
        SELECT b.name
        FROM branch b
        WHERE b.branch_id = a.open_branch_id
    ) branch,
    ->     (
        SELECT CONCAT(e.fname, ' ', e.lname)
        FROM employee e
        WHERE e.emp_id = a.open_emp_id
    ) name,
    ->     SUM(a.avail_balance) tot_deposits
    -> FROM account a
    -> GROUP BY a.product_cd, a.open_branch_id, a.open_emp_id
    -> ORDER BY 1, 2;
+------------------------+---------------+-----------------+--------------+
| product                | branch        | name            | tot_deposits |
+------------------------+---------------+-----------------+--------------+
| NULL                   | Quincy Branch | John Blake      | 50000.00     |
| NULL                   | So. NH Branch | Theresa Markham | 0.00         |
| NULL                   | Woburn Branch | Paula Roberts   | 9345.55      |
| certificate of deposit | Headquarters  | Michael Smith   | 11500.00     |
| certificate of deposit | Woburn Branch | Paula Roberts   | 8000.00      |
| checking account       | Headquarters  | Michael Smith   | 782.16       |
| checking account       | Quincy Branch | John Blake      | 1057.75      |
| checking account       | So. NH Branch | Theresa Markham | 67852.33     |
| checking account       | Woburn Branch | Paula Roberts   | 3315.77      |
| money market account   | Headquarters  | Michael Smith   | 14832.64     |
| money market account   | Quincy Branch | John Blake      | 2212.50      |
| savings account        | Headquarters  | Michael Smith   | 767.77       |
| savings account        | So. NH Branch | Theresa Markham | 387.99       |
| savings account        | Woburn Branch | Paula Roberts   | 700.00       |
+------------------------+---------------+-----------------+--------------+
14 rows in set (0.01 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

此查询与早期在from子句中使用子查询的版本之间有两个主要区别：

  • 此查询没有将product、branch和employee表与account数据进行连接，而是在select子句中使用相关标量子查询来查找产品、分支机构和员工的名称。
  • 结果集有14行，而不是11行，并且有三个产品名称为null。

结果集中多了三行的原因是，之前版本的查询包含过滤条件p.product_type_cd = 'ACCOUNT'。该过滤条件排除了产品类型为INSURANCE和LOAN（如小企业贷款）的行。由于此版本的查询没有与product表进行连接，因此无法在主查询中包含该过滤条件。针对product表的相关子查询确实包含此过滤条件，但唯一的效果是使产品名称为null。如果你想去掉多余的三行，可以将product表与account表连接并包含过滤条件，或者简单地执行以下操作：

mysql> SELECT all_prods.product,
    ->        all_prods.branch,
    ->        all_prods.name,
    ->        all_prods.tot_deposits
    -> FROM (
        SELECT 
            (
                SELECT p.name
                FROM product p
                WHERE p.product_cd = a.product_cd
                  AND p.product_type_cd = 'ACCOUNT'
            ) product,
            (
                SELECT b.name
                FROM branch b
                WHERE b.branch_id = a.open_branch_id
            ) branch,
            (
                SELECT CONCAT(e.fname, ' ', e.lname)
                FROM employee e
                WHERE e.emp_id = a.open_emp_id
            ) name,
            SUM(a.avail_balance) tot_deposits
        FROM account a
        GROUP BY a.product_cd, a.open_branch_id, a.open_emp_id
    ) all_prods
    -> WHERE all_prods.product IS NOT NULL
    -> ORDER BY 1, 2;
+------------------------+---------------+-----------------+--------------+
| product                | branch        | name            | tot_deposits |
+------------------------+---------------+-----------------+--------------+
| certificate of deposit | Headquarters  | Michael Smith   | 11500.00     |
| certificate of deposit | Woburn Branch | Paula Roberts   | 8000.00      |
| checking account       | Headquarters  | Michael Smith   | 782.16       |
| checking account       | Quincy Branch | John Blake      | 1057.75      |
| checking account       | So. NH Branch | Theresa Markham | 67852.33     |
| checking account       | Woburn Branch | Paula Roberts   | 3315.77      |
| money market account   | Headquarters  | Michael Smith   | 14832.64     |
| money market account   | Quincy Branch | John Blake      | 2212.50      |
| savings account        | Headquarters  | Michael Smith   | 767.77       |
| savings account        | So. NH Branch | Theresa Markham | 387.99       |
| savings account        | Woburn Branch | Paula Roberts   | 700.00       |
+------------------------+---------------+-----------------+--------------+
11 rows in set (0.01 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
44

只需将上一个查询包装在一个子查询（名为all_prods）中，并添加一个过滤条件以排除product列的空值，该查询现在就会返回所需的11行数据。最终结果是，该查询对account表中的原始数据进行所有分组，然后使用其他三个表中的数据来修饰输出，并且无需进行任何连接操作。

如前所述，标量子查询也可以出现在order by子句中。以下查询检索员工数据，并按每个员工上司的姓氏，然后再按员工自己的姓氏进行排序：

mysql> SELECT emp.emp_id,
    ->        CONCAT(emp.fname, ' ', emp.lname) emp_name,
    ->        (
        SELECT CONCAT(boss.fname, ' ', boss.lname)
        FROM employee boss
        WHERE boss.emp_id = emp.superior_emp_id
    ) boss_name
    -> FROM employee emp
    -> WHERE emp.superior_emp_id IS NOT NULL
    -> ORDER BY (
        SELECT boss.lname
        FROM employee boss
        WHERE boss.emp_id = emp.superior_emp_id
    ), emp.lname;
+--------+------------------+-----------------+
| emp_id | emp_name         | boss_name       |
+--------+------------------+-----------------+
| 14     | Cindy Mason      | John Blake      |
| 15     | Frank Portman    | John Blake      |
| 9      | Jane Grossman    | Helen Fleming   |
| 8      | Sarah Parker     | Helen Fleming   |
| 7      | Chris Tucker     | Helen Fleming   |
| 13     | John Blake       | Susan Hawthorne |
| 6      | Helen Fleming    | Susan Hawthorne |
| 5      | John Gooding     | Susan Hawthorne |
| 16     | Theresa Markham  | Susan Hawthorne |
| 10     | Paula Roberts    | Susan Hawthorne |
| 17     | Beth Fowler      | Theresa Markham |
| 18     | Rick Tulman      | Theresa Markham |
| 12     | Samantha Jameson | Paula Roberts   |
| 11     | Thomas Ziegler   | Paula Roberts   |
| 2      | Susan Barker     | Michael Smith   |
| 3      | Robert Tyler     | Michael Smith   |
| 4      | Susan Hawthorne  | Robert Tyler    |
+--------+------------------+-----------------+
17 rows in set (0.01 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

该查询使用了两个相关标量子查询：一个在select子句中，用于检索每个员工上司的全名；另一个在order by子句中，用于返回每个员工上司的姓氏以进行排序。

除了在select语句中使用相关标量子查询外，还可以使用非相关标量子查询为insert语句生成值。例如，假设你要生成一个新的账户行，并且你已获得以下数据：

  • 产品名称（“储蓄账户”）
  • 客户的联邦识别号（“555 - 55 - 5555”）
  • 开设账户的分支机构名称（“昆西分行”）
  • 开设账户的柜员的名字和姓氏（“弗兰克·波特曼”）

在account表中创建一行数据之前，你需要查找所有这些数据的键值，以便填充account表中的外键列。你有两种方法可以实现：执行四个查询以检索主键值，然后将这些值放入insert语句中；或者在insert语句中使用子查询来检索四个键值。以下是后一种方法的示例：

INSERT  INTO  account
(account_id,  product_cd,  cust_id,  open_date,  last_activity_date,
status,  open_branch_id,  open_emp_id,  avail_balance,  pending_balance)
VALUES   (NULL,
(SELECT  product_cd  FROM  product  WHERE  name  =   'savings  account'),
(SELECT  cust_id  FROM  customer  WHERE  fed_id  =   '555-55-5555'),
'2008-09-25',  '2008-09-25',  'ACTIVE',
(SELECT  branch_id  FROM  branch  WHERE  name  =   'Quincy  Branch'),
(SELECT  emp_id  FROM  employee  WHERE  lname  =   'Portman'  AND  fname  =   'Frank'),
0,  0);
1
2
3
4
5
6
7
8
9
10

通过一条SQL语句，你可以在account表中创建一行数据，同时查找四个外键列的值。不过，这种方法有一个缺点。当你使用子查询为允许空值的列生成数据时，即使其中一个子查询未能返回值，insert语句也会成功。例如，如果你在第四个子查询中拼错了弗兰克·波特曼的名字，account表中仍会创建一行数据，但open_emp_id将被设置为null。

# 子查询总结

本章涵盖了很多内容，所以复习一下可能是个好主意。我在本章中使用的示例展示了以下类型的子查询：

  • 返回单列单行、单列多行以及多列多行数据。
  • 独立于外部语句（非相关子查询）。
  • 引用外部语句中的一个或多个列（相关子查询）。
  • 用于使用比较运算符以及特殊用途运算符（in、not in、exists和not exists）的条件中。
  • 可以在select、update、delete和insert语句中使用。
  • 生成的结果集可以在查询中与其他表（或子查询）进行连接。
  • 可用于生成填充表或填充查询结果集中列的值。
  • 用于查询的select、from、where、having和order by子句中。

显然，子查询是一个非常通用的工具，所以如果在第一次阅读本章后，并非所有这些概念都已理解，也不要担心。继续尝试子查询的各种用途，很快你就会发现，每次编写非平凡的SQL语句时，你都会思考如何使用子查询。

# 知识测验

这些练习旨在测试你对查询的理解。答案请见附录C。

# 练习9 - 1

针对account表构造一个查询，使用对product表的非相关子查询作为过滤条件，查找所有贷款账户（product.product_type_cd = 'LOAN'）。检索账户ID、产品代码、客户ID和可用余额。

# 练习9 - 2

使用对product表的相关子查询重新编写练习9 - 1中的查询，以实现相同的结果。

# 练习9 - 3

将以下查询与employee表进行连接，以显示每个员工的经验等级：

SELECT  'trainee'   name,  '2004-01-01'  start_dt,  '2005-12-31'  end_dt
UNION  ALL
SELECT  'worker'   name,  '2002-01-01'  start_dt,  '2003-12-31'  end_dt
UNION  ALL
SELECT  'mentor'   name,  '2000-01-01'  start_dt,  '2001-12-31'  end_dt
1
2
3
4
5

给子查询取别名为levels，并包含员工ID、名字、姓氏和经验等级（levels.name）。（提示：使用不等式条件构建连接条件，以确定employee.start_date列属于哪个等级。）

# 练习9 - 4

针对employee表构造一个查询，检索员工ID、名字和姓氏，以及员工所属的部门和分支机构的名称。不要连接任何表。

第8章 分组和聚合
第10章 再谈连接

← 第10章 再谈连接→

最近更新
01
C++语言面试问题集锦 目录与说明
03-27
02
第四章 Lambda函数
03-27
03
第二章 关键字static及其不同用法
03-27
更多文章>
Copyright © 2024-2025 沪ICP备2023015129号 张小方 版权所有