跳转到内容

结构化查询语言/SELECT: 子查询

Add links
来自维基教科书，开放的书籍，开放的世界
SELECT: CASE 表达式 结构化查询语言
SELECT: 子查询 		视图



子查询是一个完整的 SELECT 命令，用于另一个 SELECT、UPDATE、INSERT 或 DELETE 命令中。它与简单的 SELECT 命令唯一的区别在于它被括在圆括号中。

分类

[编辑 | 编辑源代码]

根据创建结果的类型，子查询有三种类型

  • 标量值子查询: 子查询返回单个值，例如: (SELECT max(weight) FROM person)。
  • 行子查询: 子查询返回单个包含一个或多个值的行，例如: (SELECT min(weight), max(weight) FROM person)。
  • 表子查询: 子查询返回一个行的列表，即一个表，例如: (SELECT lastname, weight FROM person)。在分类中，结果列表中包含零行、一行或多行都没有区别。表子查询和行子查询的区别在于 **可能** 出现多行。

每种类型都可以用于其所代表的类型可能出现的所有位置：标量值子查询可以在单个值可能出现的位置使用，行子查询可以在单个行可能出现的位置使用，表子查询可以在表可能出现的位置使用。此外，表子查询可以作为 EXISTS、IN、SOME、ANY 或 ALL 谓词的参数。

独立于这种分类，子查询可以是 相关子查询 或 非相关子查询。相关子查询与周围的查询相关联，因为它们在子查询中使用周围查询的值。非相关子查询独立于周围的查询。这种区别将在 下一章 中详细说明，但也适用于其他两个子查询类。

由于相关子查询使用由周围查询确定的值，这些值可能随行而改变，因此子查询在概念上被执行的次数与周围查询的查询结果行数相同。这可能会导致性能问题。尽管如此，相关子查询仍然是一个常用的结构。在许多情况下，存在使用 JOIN 的等效结构。哪一个表现出更好的性能高度依赖于 DBMS，以及涉及的行数、索引的存在以及更多变量。

标量值子查询

[编辑 | 编辑源代码]

第一个例子创建了一个姓氏、体重和所有人的平均体重的列表。

SELECT id,
       lastname,
       weight,
       (SELECT avg(weight) FROM person) AS 'avg_weight'  -- this is the subquery
FROM   person
ORDER BY lastname;

因为子查询使用了 avg() 函数，所以 SQL 编译器知道它将返回一个唯一的值。因此，它的类型是 标量值子查询，可以在标量值可能出现的位置使用，例如 SELECT 和 FROM 之间的列表。

在下一个示例中，子查询被用作 WHERE 子句中值的代理。

-- Persons who weigh more than the average of all persons
SELECT id, lastname, weight
FROM   person
WHERE  weight >= (SELECT avg(weight) FROM person)   -- another position for the subquery
ORDER BY lastname;

这两个示例都使用了两次 person 表。也可以使用不同的表。子查询中的表名与周围查询中的表名之间没有依赖关系。这适用于所有类型的相关和非相关子查询。子查询可以从任何其他表中检索任何值，例如联系人的数量。

这两个例子展示了非相关子查询，这意味着子查询独立于它们嵌入的查询。它们只执行一次。

但应用程序经常会遇到需要子查询使用外部查询的值（类似于使用参数的子例程）的情况。这种子查询称为相关子查询。例如，下一个查询列出了人员及其家庭的平均体重。

SELECT id, firstname, lastname, weight,
       (SELECT avg(weight)
        FROM   person sq                -- 'sq' is an arbitrary alias name for the table in the subquery
        WHERE  sq.lastname = p.lastname -- identify the inner and outer table by its alias names
       ) family_average                 -- an arbitrary alias name for the computed family average
FROM   person p                         -- 'p' is an arbitrary alias name for the table in the surrounding query
ORDER BY lastname, weight;

子查询在每次从周围的 SELECT 中获取一行作为参数时，名称为 'p'。在子查询中，行 'p' 的所有列都是已知的，并且可以使用。这里，来自外部行的姓氏在子查询中用于查找家族中的所有人以及家族成员的平均体重。

注意：相关子查询为周围查询的每一行执行一次。因此，它们的成本远高于非相关子查询。可能存在使用 JOIN 或 GROUP BY 的等效解决方案，该解决方案具有更好的性能。DBMS 的查询优化器可能会在内部将给定的子查询重新排列为其中一种等效形式。但这并不适用于所有情况。

相关和非相关子查询之间的区别是通用的。它也适用于其他子查询类。

行子查询

[编辑 | 编辑源代码]

这个示例检索一个或多个人员，他们的名字是所有名字中最小（按照词典顺序）的，他们的姓氏是所有姓氏中最小的那一个。由于 AND 条件的存在，可能没有找到任何人员。

-- One resulting row: Elias Baker
SELECT *
FROM   person
WHERE  (firstname, lastname) = (SELECT MIN(firstname), MIN(lastname) FROM person);

在子查询中，检索到最小的名字和姓氏。min() 函数的使用保证不会产生超过包含两列的一行，因此它是行子查询。在周围的查询中，这个中间结果与完整表 person 的每一行进行比较，或者，如果存在，则使用索引。

幸运的是，命令检索到一行。在大多数情况下，最小的名字和姓氏来自不同的人。但即使在这些情况下，命令在语法上也是正确的，也不会抛出任何异常。

在下一个示例中，检索到每个家族中名字和姓氏最小的那些人。要做到这一点，需要使用相关行子查询。

-- 7 rows, one per family
SELECT *
FROM   person p
WHERE (firstname, lastname) =
  (SELECT MIN(firstname), MIN(lastname) FROM person sq where p.lastname = sq.lastname);

同样，person 表有两个化身，一个在周围的查询中别名为 'p'，另一个在子查询中别名为 'sq'。子查询为周围查询的每一行结果执行一次，因为 'p.lastname' 可能会随着 'p' 的每一行而改变。

每个家族中至少有一个满足条件的人，也可能有多个人满足条件。

表子查询

[编辑 | 编辑源代码]

下一个例子检索拥有联系人的那些人。子查询的类别是：非相关表子查询（用作 IN 谓词中的条件）。

SELECT * 
FROM   person 
WHERE  id IN 
  (SELECT person_id FROM contact);  -- the subquery

子查询创建多行，每行包含一列。这构成了一个新的中间表。因此，这个例子是表子查询。

IN 操作符能够作用于这个中间表。相反，不能对这种中间结果使用像 '=' 或 '>' 这样的操作符。在这种情况下，SQL 编译器会识别出语法错误。

下一个示例是第一个示例的扩展。它通过请求电子邮件地址中的姓氏来添加查询和子查询之间的相关性标准。

-- A correlated table subquery, looking for lastnames within e-mail-addresses
SELECT * 
FROM   person p
WHERE  id IN 
  (SELECT person_id 
   FROM   contact c
   WHERE  c.contact_type = 'email'
   AND    UPPER(c.contact_value) LIKE CONCAT(CONCAT('%', UPPER(p.lastname)), '%'));

AND 之后的最后一个比较有点复杂。它使用 CONCAT() 和 UPPER() 函数以及 LIKE 谓词，但这与当前主题 '子查询' 无关。重要的是子查询引用了周围查询的 'p.lastname'。只有 Goldstein 先生满足了当两列不区分大小写进行比较时，他的电子邮件地址包含他的姓氏的标准。

备注：CONCAT() 连接两个字符串。UPPER() 将字符串转换为大写。LIKE 与 '%' 符号一起使用来查找另一个字符串中的一个字符串。


接下来，有一个非相关表子查询被连接操作的例子。

-- Persons plus maximum weight of their family
SELECT *
FROM   person p
JOIN   (SELECT lastname, max(weight) max_fam_weight
        FROM   person
        GROUP BY lastname
       ) AS sq ON p.lastname = sq.lastname   -- join criterion between subquery table 'sq' and table 'p'
;

另一个例子

[编辑 | 编辑源代码]

这个例子展示了解决一个常见问题的方案。有时，有一些行描述了实体的过时状态。这些行——针对同一个逻辑实体——在某些列中彼此不同，并且还有一个额外的 version 列来跟踪时间流。

这里有 booking 示例表及其数据。

-- The table holds actual and historical values
CREATE TABLE booking (
  -- identifying columns
  id             DECIMAL      NOT NULL,
  booking_number DECIMAL      NOT NULL,
  version        DECIMAL      NOT NULL,
  -- describing columns
  state          CHAR(10)     NOT NULL,
  enter_ts       TIMESTAMP    NOT NULL,
  enter_by       CHAR(20)     NOT NULL,
  -- ...
  -- select one of the defined columns as the Primary Key
  CONSTRAINT booking_pk PRIMARY KEY (id),
  -- forbid duplicate recordings
  CONSTRAINT booking_unique UNIQUE (booking_number, version)
);

-- Add data
INSERT INTO booking VALUES (1, 4711, 1, 'created',   TIMESTAMP'2014-02-02 10:01:01', 'Emily');
INSERT INTO booking VALUES (2, 4711, 2, 'modified',  TIMESTAMP'2014-02-03 11:10:01', 'Emily');
INSERT INTO booking VALUES (3, 4711, 3, 'canceled',  TIMESTAMP'2014-02-10 09:01:01', 'John');
--
INSERT INTO booking VALUES (4, 4712, 1, 'created',   TIMESTAMP'2014-03-10 12:12:12', 'Emily');
INSERT INTO booking VALUES (5, 4712, 2, 'delivered', TIMESTAMP'2014-03-12 06:01:00', 'Charles');
--
INSERT INTO booking VALUES (6, 4713, 1, 'created',   TIMESTAMP'2014-03-11 08:50:02', 'Emily');
INSERT INTO booking VALUES (7, 4713, 2, 'canceled',  TIMESTAMP'2014-03-12 08:40:12', 'Emily');
INSERT INTO booking VALUES (8, 4713, 3, 'reopend',   TIMESTAMP'2014-03-13 10:04:32', 'Jack');
INSERT INTO booking VALUES (9, 4713, 4, 'delivered', TIMESTAMP'2014-03-15 06:40:12', 'Jack');
--
COMMIT;


问题是检索所有 实际 行，即每个预订中版本号最高的那些行。如果预订具有相同的预订号，则认为预订是相同的。

第一个解决方案使用非相关表子查询。

SELECT *
FROM   booking b
WHERE  (booking_number, version) IN
  (SELECT booking_number, MAX(version) FROM booking sq GROUP BY booking_number) -- the subquery
ORDER BY booking_number;

子查询创建了一个包含预订号及其最高版本的列表。这个列表被周围的查询用于检索具有所有列的所需行。


第二个解决方案使用相关标量值子查询。

SELECT *
FROM   booking b
WHERE  version =
  (SELECT max(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
ORDER BY booking_number;

周围的查询检索表中的所有行。对于其中的每一行，它调用子查询，该子查询检索此预订号内的最高版本。在大多数情况下，此最高版本与实际行的版本不同，并且由于 '=' 操作符的存在，这些行不是结果的一部分。只有那些其版本等于子查询中确定的值（并且其预订号与子查询中使用的预订号相同）的行是最终结果的一部分。


介绍问题的另一种方式可能是只检索一个特定预订的历史行（所有版本，除了最高版本）。

SELECT *
FROM   booking b
WHERE  version !=
  (SELECT max(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
AND    booking_number = 4711
ORDER BY version;

周围的查询将行限制为一个特定预订的那些行。子查询只为这些行调用。


很容易陷入陷阱

-- Unexpected result!
SELECT *
FROM   booking b
WHERE  version != (SELECT max(version) FROM booking)
AND    booking_number = 4711
ORDER BY version;

上面的查询返回了所有版本号为 4711 的预订，包括当前版本！为了获得预期的结果，需要将周围的查询和子查询关联起来。

练习

[编辑 | 编辑源代码]

查找拥有最多版本的预订。

点击查看解决方案
-- The subselect return exactly ONE single value. Therefor it's a (non-correlated) single value subquery.
-- But this is only a intermediate result. The final result may contain several rows, which is not the case in our example database!
SELECT *
FROM   booking
WHERE  version = (SELECT MAX(version) FROM booking);

查找所有已取消的预订（在最新版本中）。

点击查看解决方案
-- It's necessary to link the subquery with the surrounding query.
SELECT *
FROM   booking b
WHERE  version =
  (SELECT MAX(version) FROM booking sq WHERE sq.booking_number = b.booking_number)
AND    state = 'canceled';

-- Additionally within the resulting rows there must be a correlation between the version and the state.
-- This is accomplished with the AND keyword at the level of the surrounding query. If the AND works within
-- the subquery, the result does not meet the expectations.
SELECT *
FROM   booking b
WHERE  version =
  (SELECT MAX(version) FROM booking sq WHERE sq.booking_number = b.booking_number AND state = 'canceled');

创建所有人员列表。对于每个人，请包含与该人生在同一城市的其他人数量。

点击查看解决方案
-- The subselect uses the place_of_birth of the outside row. Therefore it's a correlated subquery.
SELECT firstname,
       lastname,
       place_of_birth,
       (SELECT COUNT(*) FROM person sq WHERE p.place_of_birth = sq.place_of_birth) cnt -- an arbitrary name for the additional column
FROM   person p;

创建所有人员列表以及他们的联系信息数量。

点击查看解决方案
-- The subselect uses the ID of the outside row. Therefore it's a correlated subquery.
SELECT firstname,
       lastname,
       (SELECT COUNT(*) FROM contact c WHERE p.id = c.person_id) cnt -- an arbitrary name for the additional column
FROM   person p;

创建所有人员列表以及他们的电子邮件地址数量。

点击查看解决方案
SELECT firstname,
       lastname,
       (SELECT COUNT(*)
        FROM   contact c
        WHERE  p.id = c.person_id
        AND    contact_type = 'email'  -- The subselect is a complete SELECT. Therefor all elements of
                                       -- a 'regular' SELECT may be used: Join, functions, ... and: SUBSELECT
       ) cnt                           -- an arbitrary name of the additional column
FROM   person p;

创建所有人员列表以及他们的联系信息数量。（与上一个问题相同。）
用 JOIN 结构替换子查询。

点击查看解决方案
-- Step 1 (for demonstration purpose only): To retrieve ALL persons, it's necessary to use an OUTER JOIN
SELECT firstname,
       lastname,
       c.contact_type
FROM   person p
LEFT OUTER JOIN contact c ON p.id = c.person_id;
-- 
-- Step 2 (complete solution): Add the counter. To do so, the result must be grouped.
SELECT firstname,
       lastname,
       count(c.contact_type)
FROM   person p
LEFT OUTER JOIN contact c ON p.id = c.person_id
GROUP BY firstname, lastname;

哪些人员没有联系信息？

点击查看解决方案
-- The subquery returns more than one row. Therefore it's a table subquery.
SELECT firstname, lastname
FROM   person
WHERE  id NOT IN (SELECT person_id FROM contact);  -- the subquery


SELECT: CASE 表达式 结构化查询语言
SELECT: 子查询 		视图
检索自 "https://wikibooks.cn/w/index.php?title=Structured_Query_Language/SELECT:_Subquery&oldid=3819559"
华夏公益教科书