PostgreSQL 子查询

子查询（Subquery）是指嵌套在其他SELECT、INSERT、UPDATE以及DELETE语句中的查询语句。子查询的作用与多表连接查询有点类似，也是为了从多个关联的表中返回或者过滤数据。例如，我们想要知道哪些员工的月薪大于平均月薪，可以通过子查询实现：

SELECT e.first_name, e.last_name, e.salary FROM employees e
WHERE salary > (SELECT avg(salary) FROM employees);

其中，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;
Purchasing 24900
Human Resources    6500
Shipping    156400
IT    28800
Public Relations    10000

其中，子查询返回了部门编号和部门月薪合计；然后再和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');
50    Shipping
80    Sales

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

ALL操作符

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

SELECT first_name, last_name, salary
FROM employees
WHERE salary > all (SELECT salary FROM employees WHERE department_id = 80);
Steven    King    24000.00
Neena    Kochhar    17000.00
Lex    De Haan    17000.00

此场景效果等价于

SELECT first_name, last_name, salary
FROM employees
WHERE salary >   (SELECT max(salary) FROM employees WHERE department_id = 80);

如果是< all 则子查询需要些min

ANY操作符

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

SELECT first_name, last_name, salary
FROM employees
WHERE salary > any (SELECT salary FROM employees WHERE department_id = 80);

以上语句返回了月薪比销售部门（department_id = 80）任何员工高的员工。ANY也可以和其他比较运算符一起使用，例如= ANY实际上和IN的作用相同。另外，SOME和ANY是同义词。

横向子查询

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

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

以上语句在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
Administration    4400
Marketing    19000
Purchasing    24900
Human Resources    6500
Shipping    156400

效果等价于

select department_name, sum_salary
from departments d
         left join (select department_id, sum(salary) sum_salary from employees group by department_id) t
                   on d.department_id = t.department_id;

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');
50    Shipping
80    Sales

示例返回了存在2008年01月01日以后入职员工的部门，与上文中的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 department_id not in (SELECT department_id FROM employees WHERE department_id = d.department_id);