likes
comments
collection
share

PostgreSQL技术问答09 - Subquery

作者站长头像
站长
· 阅读数 57

本文是《PostgreSQL技术问答》系列文章中的一篇。关于这个系列的由来，可以参阅开篇文章：

文章的编号只是一个标识，在系列中没有明确的逻辑顺序和意义。读者进行阅读时，不用太关注这个方面。

本文主要讨论的内容是子查询 subquery。

什么是Subquery(子查询)

Subquery，从字面上理解，就是子查询，可以理解成为一个查询的结果，可以以某种方式被主查询使用，来构成一定的业务关系，也可以理解成为查询的嵌套。实际上，这样的说法，并不是很完整。查询Postgres相关技术文档，我们可以看到，子查询完整的说法，应当是“子查询表达式和函数”。这个意思是说，子查询的内容是一个查询表达式，它应该被作为参数用作一个子查询函数中，作为完整SQL语句的一个组成部分。

子查询的内容，就是一个普通的查询；而其配套的子查询函数和应用，包括以下几种方式：

  • exists 存在检查
  • in / not in 包含或者不包含
  • any / some
  • all
  • single-row comparision 单行比较

子查询一般用于在进行数据查询或者操作的时候，构造数据过滤条件。它可能有以下几种场景和用法：

  • 简化复杂查询

就是将一个复杂的问题进行分解，更贴近人类的思维过程和方式。

  • 分步操作

子查询有助于将复杂查询分解为某些操作步骤，方便思维、操作和理解。

  • 共享结果集

如果在查询中，需要使用多个相同的查询结果，可以使用子查询或者配合CTE来共享查询操作和结果。

  • 增强可读性

通常使用子查询可以让复杂的查询更容易分解成为多个容易理解的组成部分和关联关系，方便代码的维护和更新。

在特定的场景下，使用子查询的结构，可能有助于简化逻辑关系的表达，方便相关条件查询代码和SQL语句的编写。

子查询相关的官方技术文档地址是：

www.postgresql.org/docs/16/fun…

下面，我们结合子查询的几个函数，分别讨论它们的使用场景、方法和特点。

如何理解exists

exists是一个子查询表达式方法，从方法名称我们就可以知道，它通常用作基于检查存在性并作为过滤条件。也就是说，exists方法的输入是一个查询(记录集)，它的输出是一个布尔值，并作用于当前这个查询记录集的行。由于只需要做存在性检查，所以如果子查询设计的合理，用在适合的场景中，它的执行效率和性能在理论上可以更高。

比如下面的例子：

DELETE FROM customers WHERE customer_id = 123
AND NOT EXISTS ( SELECT 1 FROM orders  WHERE customer_id = 123 ) returning * ;

这个例子可以实现这样一个需求，就是在删除记录前，检查一下在另一个表中，是否有关联和引用的记录。对于特定的记录而言，它只需要检查是否至少有一条关联记录即可用于判断，而不需要使用表连接操作，甚至都不需要返回记录的内容，显然更加合理高效。

再来看一个例子：

SELECT * FROM customers
WHERE EXISTS (
  SELECT * FROM orders
  WHERE customer_id = customers.id
  AND DATE_FORMAT(order_date, '%Y-%m') = '2022-03'
)
AND NOT EXISTS (
  SELECT * FROM orders
  WHERE customer_id = customers.id
  AND DATE_FORMAT(order_date, '%Y-%m') = '2022-04'
);

上面的例子，实现的是这样一种查询需求，就是检查满足一种条件，同时不满足另外一种条件的记录的类型。这种检查体现了两种应用方式，第一是快速检查记录的存在性或者不存在性，另外就是可以将检查条件组合起来使用。还可以看到，在exists之前，可以加上not来做一个“非”运算，来适合更多的应用场景。

子查询和in操作是如何使用的

IN 子查询是一种 SQL 子查询，用于检查某个值是否在一个子查询结果集中。IN 子查询通常用于 WHERE 子句中，用来过滤出满足某些条件的行。in子查询参数，所使用的参数SQL语句有一定的限制条件，就是它的结果集只有一个字段。而且in子查询返回的结果是一个记录集而非exists的布尔值。所以，除了in子查询之外，用于条件检查的，还需要配合一个值的表达式，而不能直接使用。in子查询的标准形式是：

expression IN (subquery)

举个例子：

SELECT * FROM employees WHERE department_id IN (SELECT department_id FROM departments);

这里查询的是在所有在部门表中，有部门ID的部门中，所有的雇员的记录。因为在雇员表中，还可能包含没有有效部门ID的记录，这个操作可以保证数据的完整性和可参照性。

需要注意，虽然和SQL语句中的in表达式的形式相似，但实际上的概念差异是很大的。子查询的结果是一个记录集，而in语句使用的是一个值的列表。

对于需要比较多个字段的情况，Postgres的in子查询，也提供了相应的查询方式如下：

row_constructor IN (subquery)

下面是一个简单的例子：


SELECT * FROM employees WHERE row(department_id,gender) 
IN (SELECT department_id, 2 as gender FROM departments);

这里的row constructor(行构造器)是一种用于创建行值的表达式。这里行值是一种数据类型，它由一个或多个列值组成，每个列值都有一个特定的数据类型。在很多时候ROW关键字是可以省略的，上面的语句可以简写成为 (department_id,gender) = ....，它应当是等效于多个条件的and组合。

Any/Some/All表达式如何理解

在使用子查询构造条件比较的时候，可以使用集合逻辑计算的方式。它们主要有两种逻辑关系，即集合包含和集合匹配。集合包含是指集合中任意元素都匹配某个条件，可以用Any和Some来表达，这里and和some其实是等效的，都表示对于集合中任意元素的条件检查结果，返回值是布尔型；集合匹配指集合中所有的元素都满足某个条件，返回值同样是一个布尔，即条件检查的结果。它们的标准形式是：

expression operator ANY (subquery)

expression operator SOME (subquery) // 等效于ANY

expression operator ALL (subquery)

下面是几个简单的例子，方便读者理解：


// 使用相等操作符进行检查
SELECT * FROM employees WHERE 
department_id = ANY (SELECT department_id FROM departments WHERE location_id = 1700);

// 等效于SOME
SELECT * FROM employees WHERE 
department_id = SOME (SELECT department_id FROM departments WHERE location_id = 1700);

// 使用另一种操作符
SELECT * FROM employees WHERE 
salary < ANY (SELECT max_salary FROM departments WHERE location_id = 1700);

可以看到，这里面有几个要素。首先是子查询来构造一个简单列表(单字段记录集)；然后使用逻辑关键字(Any/Some/All)配合操作符(operator，如相等=、大于>...)来表明需要进行的逻辑操作；最后就是需要比较的对象，一般来自数据表的字段值。还可以看到，这些表达式的使用方式，和in子查询的差异，在于需要使用一个条件检查操作符，来进行条件的判断，而不是简单的进行包含性检查。子查询的限制也是一样的，就是其结果是一个单字段的记录集，才能进行相关的逻辑操作。

什么是Single-Row Comparison(单行比较)

单行比较是指使用操作符，来结合子查询进行单条记录值的条件检查。由于使用严格限定的比较操作符，子查询结果被限定在返回单行单列的值(或者空行)，就是所谓的标量子查询(Scalar Subquery)，即子查询的结果是一个简单值，而不是一行或者一个记录值。这个标量可以是任何数据类型，例如数字、字符串、日期等。所以，有时单行比较在Postgres中，也被成为标量子查询(Scalar Subquery)。下面是一个标量子查询的示例：


SELECT * FROM employees WHERE manager_id = (SELECT manager_id FROM departments WHERE department_id = 10);

如果不能将子查询结果限制在标量，这个查询会产生以下错误：

ERROR: more than one row returned by a subquery used as an expression 这个错误消息表示子查询返回了多行结果，而单行比较只能处理单行结果。

为了解决这个问题，可以使用其他的方式来处理多行结果，例如使用 IN 运算符或 EXISTS 运算符等。我们也可以认为，标量子查询是in子查询的一个特例。

笔者觉得，单行比较和exists比较类似，但exists可以直接返回布尔结果，而单行比较则返回的是一个标量和另一个标量表达式的比较结果。

小结

本文从Postgres技术文档的相关内容出发，重新思考和探讨了子查询这一功能特性。讨论了其基本结构，标准语法，相关的关键字形式，使用场景和需要注意的问题等等。

转载自:https://juejin.cn/post/7380688287551176723
评论
请登录