《PostgreSQL 开发指南》第 18 篇子查询

《SQL编程思想》作者，PostgreSQL ACE。

为了方便大家阅读学习，制作了专栏的电子版PDF，免费开放下载：https://github.com/dongxuyang1985/postgresql_dev_guide

子查询（Subquery）是指嵌套在其他SELECT、INSERT、UPDATE以及DELETE语句中的查询语句。

子查询的作用与多表连接查询有点类似，也是为了从多个关联的表中返回或者过滤数据。例如，我们想要知道哪些员工的月薪大于平均月薪，可以通过一个子查询实现：

select e.first_name, e.last_name, e.salary
  from employees e
 where salary > (select avg(salary) from employees);
first_name |last_name |salary  |
-----------|----------|--------|
Steven     |King      |24000.00|
Neena      |Kochhar   |17000.00|
Lex        |De Haan   |17000.00|
...

其中，WHERE子句中使用了一个子查询，用于计算平均月薪。子查询必须位于括号中，也称为内查询，包含子查询的查询语句被称为外查询。

PostgreSQL 在执行以上语句时，先执行子查询返回平均月薪；然后将该值传递给外查询使用。

除了WHERE子句之外，其他子句中也可以使用子查询，例如 SELECT 列表、FROM 子句等。

派生表

出现在 FROM 子句中的子查询被称为派生表（Derived table），语法如下：

SELECT column1, column2, ...
  FROM (subquery) AS table_alias;

其中子查询相当于创建了一个临时表 table_alias。以下语句用于获取每个部门的总月薪：

select d.department_name,
       ds.sum_salary
  from departments d
  join (select department_id,
               SUM(salary) as sum_salary
          from employees
         group by department_id) ds
    on (d.department_id = ds.department_id);
department_name |sum_salary|
----------------|----------|
Administration  |   4400.00|
Marketing       |  19000.00|
Purchasing      |  24900.00|
...

其中，子查询返回了部门编号和部门月薪合计；然后再和 departments 表进行连接查询。

IN 操作符

如果WHERE子查询返回多个记录，可以使用IN操作符进行条件过滤：

select d.department_id,
       d.department_name 
  from departments d
 where d.department_id in (select department_id from employees where hire_date >= date '2008-01-01');
department_id|department_name|
-------------|---------------|
           50|Shipping       |
           80|Sales          |

以上查询返回了存在 2008 年 1 月 1 日以后入职员工的部门。如果想要返回包含该日期之前入职的员工的部门，可以使用NOT IN操作符。

除了IN之外，还有一些其他进行类似过滤的操作符。

ALL 操作符

ALL操作符与比较运算符一起使用，可以将一个值与子查询返回的列表进行比较：

select first_name, last_name, salary
  from employees
 where salary > all (select salary from employees where department_id = 80);
first_name|last_name|salary  |
----------|---------|--------|
Steven    |King     |24000.00|
Neena     |Kochhar  |17000.00|
Lex       |De Haan  |17000.00|

以上语句返回了月薪比销售部门（department_id = 80）所有员工都高的员工。

其他比较运算符也可以与ALL进行组合，例如salary < ALL表示月薪比销售部门所有员工都低的员工。

ANY 操作符

ANY操作符和ALL操作符使用方法类似，只是效果不同：

select first_name, last_name, salary
  from employees
 where salary > any (select salary from employees where department_id = 80);
first_name |last_name |salary  |
-----------|----------|--------|
Steven     |King      |24000.00|
Neena      |Kochhar   |17000.00|
Lex        |De Haan   |17000.00|
Alexander  |Hunold    | 9000.00|
...

以上语句返回了月薪比销售部门（department_id = 80）任何员工高的员工。

ANY也可以和其他比较运算符一起使用，例如= ANY实际上和IN的作用相同。

另外，SOME和ANY是同义词。

关联子查询

以上所有示例中的子查询都可以独立运行，因为它们没有使用到外部查询中的信息。还有另一类子查询，它们会引用外部查询中的列，因而与外部查询产生关联，被称为关联子查询。

以下语句返回月薪大于所在部门平均月薪的员工：

select first_name, last_name, salary
from employees o
where o.salary > (select avg(salary) from employees i where i.department_id = o.department_id);

我们可以看到，子查询中使用了外查询的字段（o.department_id）。对于外部查询中的每个员工，运行子查询返回他/她所在部门的平均月薪，然后传递给外部查询进行判断。

关联子查询对于外查询中的每一行都会运行一次（数据库可能会对此进行优化），而非关联子查询在整个查询运行时只会执行一次。

以下语句在SELECT列表中使用关联子查询，返回每个部门的总月薪，和上文第一个示例相同：

SELECT d.department_name,
       (SELECT SUM(salary)
          FROM employees e
         WHERE e.department_id = d.department_id) AS sum_salary
  FROM departments d
  ORDER BY d.department_name;

横向子查询

一般来说，子查询只能引用外查询中的字段，而不能使用同一层级中其他表中的字段。例如：

-- Error case
select d.department_name,
       t.avg_salary
  from departments d
  join (select avg(e.salary) as avg_salary
          from employees e
         where e.department_id = d.department_id) t;
SQL Error [42601]: ERROR: syntax error at end of input
  Position: 209

以上语句在JOIN中引用了左侧 departments 表中的字段，产生了语法错误。为此，我们需要使用横向子查询（LATERAL subquery）。通过增加LATERAL关键字，子查询可以引用左侧表中的列：

select d.department_name,
       t.sum_salary
  from departments d
 cross join lateral (select sum(e.salary) as sum_salary
          from employees e
         where e.department_id = d.department_id) t;

以上语句同样返回了每个部门的名称和总月薪。

EXISTS 操作符

EXISTS操作符用于检查子查询结果的存在性。如果子查询返回任何结果，EXISTS返回 true；否则，返回 false。

select d.department_id,
       d.department_name
  from departments d
 where exists (select 1 from employees where department_id = d.department_id and hire_date >= date '2008-01-01');

以上示例返回了存在 2008 年 1 月 1 日以后入职员工的部门，与上文中的IN操作符示例相同。

NOT EXISTS操作符执行相反的操作，即子查询不返回任何结果，NOT EXISTS返回 true；否则，返回 false。

[NOT] IN用于检查某个值是否属于（=）子查询的结果列表，[NOT] EXISTS只检查子查询结果的存在性。如果子查询的结果中存在 NULL，NOT EXISTS结果为 true；但是，NOT IN结果为 false，因为NOT (X = NULL)的结果为 NULL。例如：

select d.department_id,
       d.department_name
  from departments d
 where not exists (select 1 from employees where department_id = d.department_id);

以上语句查找没有任何员工的部门，结果返回了 16 条记录。如果使用NOT IN操作符：

select d.department_id,
       d.department_name
  from departments d
 where d.department_id not in (select department_id from employees);

查询没有返回任何结果，因为有一个员工不属于任何部门，导致子查询的结果中包含 NULL 值：

select first_name, last_name, department_id
  from employees
 where department_id is null;
first_name|last_name|department_id|
----------|---------|-------------|
Kimberely |Grant    |             |

欢迎关注❤️、点赞、评论

编辑于 2022-04-27 10:53

PostgreSQL

数据库

数据分析