子查询是指嵌套在一个查询中的查询。TDSQL PG支持多层嵌套查询。在特定情况下，一个查询语句的条件需要从另一个查询语句中获取，内层查询（inner query）语句的查询结果，可以为外层查询（outer query）语句提供查询条件。
子查询按不同的维度，可以有不同的划分。按子查询和父查询的依赖关系划分，可以分为相关子查询和不相关子查询。
按子查询结果集划分，可以分为标量子查询，行子查询，列子查询和表子查询。
语法及关键字
语法说明：
- 子查询可以在大部分的DDL（如CREATE TABLE/VIEW）以及DML（SELECT、INSERT、UPDATE）语句中使用。
- 子查询需要放在小括号中。
- 子查询中的关键词可以使用 IN、ANY、SOME 和 ALL 等。
示例表
---职员信息表
CREATE TABLE EMPLOYEES(
EMPLOYEE_ID VARCHAR(100),
NAME VARCHAR(100),
GENDER VARCHAR(6),
BIRTHDAY DATE,
EMAIL VARCHAR(100),
SALARY NUMERIC,
MANAGER_ID VARCHAR(100),
DEPARTMENT_ID VARCHAR(100));
---插入数据
INSERT INTO EMPLOYEES VALUES('0001', 'john', 'male', '1983-3-4', 'john@gmail.com', 6500, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0002', 'andy', 'male', '1987-8-9', 'andy@gmail.com', 8000, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0003', 'bob', 'male', '1985-01-04', 'bob@gmail.com', 12000, '0082', '005');
INSERT INTO EMPLOYEES VALUES('0004', 'eric', 'male', '1997-02-18', 'eric@gmail.com', 7000, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0005', 'david', 'male', '1990-6-12', 'david@gmail.com', 9000, '0082', '005');
INSERT INTO EMPLOYEES VALUES('0006', 'frank', 'male', '1982-12-19', 'frank@gmail.com', 8800, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0007', 'henry', 'male', '1993-03-04', 'henry@gmail.com', 11000, '0082', '005');
INSERT INTO EMPLOYEES VALUES('0008', 'emily', 'female', '1989-5-4', 'emily@gmail.com', 8500, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0009', 'lucy', 'female', '1992-1-3', 'lucy@gmail.com', 6500, '0081', '003');
INSERT INTO EMPLOYEES VALUES('0010', 'julia', 'female', '1991-12-30', 'julia@gmail.com', 7000, '0082', '005');
INSERT INTO EMPLOYEES VALUES('0011', 'leo', 'male', '1983-12-20', 'leo@gmail.com', 14000, '0081', '003');
相关子查询
子查询对父查询有所依赖，子查询并不能独立运行的查询。
示例1
select * from employees e1 where DEPARTMENT_ID in (select DEPARTMENT_ID from employees e2 where e2.salary < e1.salary);
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+---------------------+--------------+--------+------------+---------------
0002 | andy | male | 1987-08-09 00:00:00 | andy@gmail.com | 8000 | 0081 | 003
0003 | bob | male | 1985-01-04 00:00:00 | bob@gmail.com | 12000 | 0082 | 005
0004 | eric | male | 1997-02-18 00:00:00 | eric@gmail.com | 7000 | 0081 | 003
0005 | david | male | 1990-06-12 00:00:00 | david@gmail.com | 9000 | 0082 | 005
0006 | frank | male | 1982-12-19 00:00:00 | frank@gmail.com | 8800 | 0081 | 003
0007 | henry | male | 1993-03-04 00:00:00 | henry@gmail.com | 11000 | 0082 | 005
0008 | emily | female | 1989-05-04 00:00:00 | emily@gmail.com | 8500 | 0081 | 003
0011 | leo | male | 1983-12-20 00:00:00 | leo@gmail.com | 14000 | 0081 | 003
(8 rows)
不相关子查询
子查询对父查询没有依赖，可以独立执行的查询。
示例2
select * from employees where gender in (select 'male' from dual);
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+---------------------+--------------+--------+------------+---------------
0001 | john | male | 1983-03-04 00:00:00 | john@gmail.com | 6500 | 0081 | 003
0002 | andy | male | 1987-08-09 00:00:00 | andy@gmail.com | 8000 | 0081 | 003
0003 | bob | male | 1985-01-04 00:00:00 | bob@gmail.com | 12000 | 0082 | 005
0004 | eric | male | 1997-02-18 00:00:00 | eric@gmail.com | 7000 | 0081 | 003
0005 | david | male | 1990-06-12 00:00:00 | david@gmail.com | 9000 | 0082 | 005
0006 | frank | male | 1982-12-19 00:00:00 | frank@gmail.com | 8800 | 0081 | 003
0007 | henry | male | 1993-03-04 00:00:00 | henry@gmail.com | 11000 | 0082 | 005
0011 | leo | male | 1983-12-20 00:00:00 | leo@gmail.com | 14000 | 0081 | 003
(8 rows)
标量子查询
返回的结果集是 1 行 1 列
示例3
--查询薪水大于平均薪水的所有员工信息
select * from EMPLOYEES where salary > (select avg(salary) from EMPLOYEES);
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+------------+--------------+--------+------------+---------------
0003 | bob | male | 1985-01-04 | bob@gmail.com | 12000 | 0082 | 005
0005 | david | male | 1990-06-12 | david@gmail.com | 9000 | 0082 | 005
0007 | henry | male | 1993-03-04 | henry@gmail.com | 11000 | 0082 | 005
0011 | leo | male | 1983-12-20 | leo@gmail.com | 14000 | 0081 | 003
(4 rows)
行子查询
返回的结果集是 1 行 N 列
示例4
---查询与eric属于同一部门，且性别相同的所有员工信息
select * from EMPLOYEES where (department_id, gender) = (select department_id, gender from EMPLOYEES where name = 'eric');
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+------------+--------------+--------+------------+---------------
0001 | john | male | 1983-03-04 | john@gmail.com | 6500 | 0081 | 003
0002 | andy | male | 1987-08-09 | andy@gmail.com | 8000 | 0081 | 003
0004 | eric | male | 1997-02-18 | eric@gmail.com | 7000 | 0081 | 003
0006 | frank | male | 1982-12-19 | frank@gmail.com | 8800 | 0081 | 003
0011 | leo | male | 1983-12-20 | leo@gmail.com | 14000 | 0081 | 003
(5 rows)
列子查询
返回的结果集是 N 行 1 列
列子查询通常使用 IN, ANY, ALL 和 SOME 操作符
- IN
返回布尔型，如果属于列表中任意一个，返回 true，否则返回 false - ANY
返回布尔型，需要与比较操作符配合使用，如果集合中任意一个满足条件，返回 true，否则返回 false - SOME
与 ANY 作用相同 - ALL
返回布尔型，需要与比较操作符配合使用，集合中所有元素满足条件才返回 true，否则返回 false
示例5
---查询名字以 d 或 f 开头的员工信息
select * from EMPLOYEES where substr(name, 1, 1) in (select 'd' union select 'f');
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+------------+--------------+--------+------------+---------------
0005 | david | male | 1990-06-12 | david@gmail.com | 9000 | 0082 | 005
0006 | frank | male | 1982-12-19 | frank@gmail.com | 8800 | 0081 | 003
(2 rows)
示例6
---查询工资大于 8000 或者 10000 的员工
select * from EMPLOYEES where salary > any (select 8000 union select 10000);
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+------------+--------------+--------+------------+---------------
0003 | bob | male | 1985-01-04 | bob@gmail.com | 12000 | 0082 | 005
0005 | david | male | 1990-06-12 | david@gmail.com | 9000 | 0082 | 005
0006 | frank | male | 1982-12-19 | frank@gmail.com | 8800 | 0081 | 003
0007 | henry | male | 1993-03-04 | henry@gmail.com | 11000 | 0082 | 005
0008 | emily | female | 1989-05-04 | emily@gmail.com | 8500 | 0081 | 003
0011 | leo | male | 1983-12-20 | leo@gmail.com | 14000 | 0081 | 003
(6 rows)
示例7
---查询工资大于 8000 和 10000 的员工
select * from EMPLOYEES where salary > all (select 8000 union select 10000);
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+------------+--------------+--------+------------+---------------
0003 | bob | male | 1985-01-04 | bob@gmail.com | 12000 | 0082 | 005
0007 | henry | male | 1993-03-04 | henry@gmail.com | 11000 | 0082 | 005
0011 | leo | male | 1983-12-20 | leo@gmail.com | 14000 | 0081 | 003
(3 rows)
表子查询
返回的结果集是 N 行 N 列
表子查询与列子查询类似，区别是返回的是多列，需要与 IN, ANY, ALL 配合使用。
示例8
select * from employees where (gender, department_id) in (select 'male', 003 from dual);
EMPLOYEE_ID | NAME | GENDER | BIRTHDAY | EMAIL | SALARY | MANAGER_ID | DEPARTMENT_ID
-------------+-------+--------+---------------------+--------------+--------+------------+---------------
0001 | john | male | 1983-03-04 00:00:00 | john@gmail.com | 6500 | 0081 | 003
0002 | andy | male | 1987-08-09 00:00:00 | andy@gmail.com | 8000 | 0081 | 003
0004 | eric | male | 1997-02-18 00:00:00 | eric@gmail.com | 7000 | 0081 | 003
0006 | frank | male | 1982-12-19 00:00:00 | frank@gmail.com | 8800 | 0081 | 003
0011 | leo | male | 1983-12-20 00:00:00 | leo@gmail.com | 14000 | 0081 | 003
(5 rows)