当前位置：首页 > news >正文

【Oracle数据库指南】第05篇：Oracle子查询与集合操作——嵌套查询与结果合并全解析

news 2026/5/11 2:36:20

上一篇【第04篇】Oracle多表查询与连接操作——JOIN的全面解析
下一篇【第06篇】Oracle DML语句与事务控制——数据操作与ACID特性深度解析（明日更新，敬请期待）

摘要

本文深入讲解Oracle SQL中的子查询技术，涵盖单行子查询、多行子查询、多列子查询、相关子查询、内联视图，以及集合操作（UNION、INTERSECT、MINUS）的使用方法和适用场景。通过大量实战案例，帮助读者掌握复杂查询逻辑的构建技能，编写出简洁高效的SQL语句。

一、引言

当一个查询的结果需要依赖另一个查询的结果时，就需要使用子查询（Subquery）。子查询是嵌套在主查询中的SELECT语句，可以出现在：

WHERE子句中：作为筛选条件
FROM子句中：作为内联视图（行内视图）
SELECT子句中：作为标量子查询
HAVING子句中：作为分组过滤条件

子查询按返回结果分类：

单行子查询：返回一行一列（使用=、>、<、>=、<=、<>等单值比较运算符）
多行子查询：返回多行一列（使用IN、ANY、ALL运算符）
多列子查询：返回一行或多行多列

二、单行子查询

2.1 在WHERE中使用单行子查询

-- 查询薪资高于平均薪资的员工SELECTemployee_id,last_name,salaryFROMemployeesWHEREsalary>(SELECTAVG(salary)FROMemployees)ORDERBYsalaryDESC;-- 查询与员工178（Kimberely Grant）同职位的员工SELECTemployee_id,last_name,job_idFROMemployeesWHEREjob_id=(SELECTjob_idFROMemployeesWHEREemployee_id=178)ANDemployee_id<>178;-- 查询薪资最高的员工信息SELECTemployee_id,last_name,salaryFROMemployeesWHEREsalary=(SELECTMAX(salary)FROMemployees);-- 查询最晚入职的员工信息SELECTemployee_id,first_name,last_name,hire_dateFROMemployeesWHEREhire_date=(SELECTMAX(hire_date)FROMemployees);

2.2 在HAVING中使用单行子查询

-- 查询平均薪资高于IT部门平均薪资的部门SELECTdepartment_id,ROUND(AVG(salary),2)ASdept_avg_salaryFROMemployeesGROUPBYdepartment_idHAVINGAVG(salary)>(SELECTAVG(salary)FROMemployees eJOINdepartments dONe.department_id=d.department_idWHEREd.department_name='IT')ORDERBYdept_avg_salaryDESC;-- 查询员工人数最多的部门信息SELECTdepartment_id,COUNT(*)ASemp_countFROMemployeesGROUPBYdepartment_idHAVINGCOUNT(*)=(SELECTMAX(COUNT(*))FROMemployeesGROUPBYdepartment_id)ORDERBYdepartment_id;

2.3 子查询返回NULL的情况

-- 如果子查询返回NULL，外层的比较运算符结果为UNKNOWN（不满足条件）-- 以下查询会返回空结果集，因为NULL > 任何值都是UNKNOWNSELECT*FROMemployeesWHEREsalary>(SELECTMIN(salary)FROMemployeesWHEREdepartment_id=999);-- department_id = 999不存在，子查询返回NULL，所以外层查询无结果-- 解决方案：使用NVL处理子查询可能返回NULL的情况SELECT*FROMemployeesWHEREsalary>NVL((SELECTMIN(salary)FROMemployeesWHEREdepartment_id=999),0);

三、多行子查询

3.1 IN运算符

-- 查询在IT或Finance部门工作的员工SELECTemployee_id,last_name,department_idFROMemployeesWHEREdepartment_idIN(SELECTdepartment_idFROMdepartmentsWHEREdepartment_nameIN('IT','Finance'));-- 等价写法（多表连接更高效）SELECTe.employee_id,e.last_name,e.department_idFROMemployees eJOINdepartments dONe.department_id=d.department_idWHEREd.department_nameIN('IT','Finance');-- NOT IN：查询不在指定部门的员工SELECTemployee_id,last_name,department_idFROMemployeesWHEREdepartment_idNOTIN(SELECTdepartment_idFROMdepartmentsWHERElocation_id=1700);-- 警告：如果子查询结果包含NULL，NOT IN将返回空结果集！-- 因为：任何值 NOT IN (包含NULL的集合) = FALSE

3.2 ANY运算符

ANY（也写作SOME）：与子查询结果中任意一个值比较，满足其中一个即可。

-- > ANY：大于子查询结果中最小的值SELECTemployee_id,last_name,salaryFROMemployeesWHEREsalary>ANY(SELECTsalaryFROMemployeesWHEREdepartment_id=60)ANDdepartment_id<>60;-- 等价于：salary > MIN(某部门薪资)-- < ANY：小于子查询结果中最大的值SELECTemployee_id,last_name,salaryFROMemployeesWHEREsalary<ANY(SELECTsalaryFROMemployeesWHEREjob_id='SA_MAN')ORDERBYsalary;-- 等价于：salary < MAX(销售经理薪资)-- = ANY：等同于 INSELECTemployee_id,last_name,department_idFROMemployeesWHEREdepartment_id=ANY(SELECTdepartment_idFROMdepartmentsWHERElocation_id=1700);

3.3 ALL运算符

ALL：与子查询结果中所有值比较，需要满足所有比较。

-- > ALL：大于子查询结果中最大的值SELECTemployee_id,last_name,salaryFROMemployeesWHEREsalary>ALL(SELECTsalaryFROMemployeesWHEREdepartment_id=60)ORDERBYsalary;-- 等价于：salary > MAX(IT部门薪资)-- < ALL：小于子查询结果中最小的值SELECTemployee_id,last_name,salaryFROMemployeesWHEREsalary<ALL(SELECTsalaryFROMemployeesWHEREjob_id='SA_MAN');-- 等价于：salary < MIN(销售经理薪资)-- != ALL：不等于子查询结果中的任何值（等同于NOT IN，但NULL处理不同）SELECTemployee_id,last_name,department_idFROMemployeesWHEREdepartment_id!=ALL(SELECTdepartment_idFROMdepartmentsWHERElocation_id=1800);

3.4 EXISTS与NOT EXISTS

EXISTS检查子查询是否返回至少一行，通常比IN更高效（尤其是子查询结果集很大时）。

-- 查询有至少一名员工的部门SELECTdepartment_id,department_nameFROMdepartments dWHEREEXISTS(SELECT1FROMemployees eWHEREe.department_id=d.department_id);-- 注意：子查询中的SELECT 1只需要确认行存在，不需要实际返回数据-- 等价的IN写法（数据量大时通常慢于EXISTS）SELECTdepartment_id,department_nameFROMdepartmentsWHEREdepartment_idIN(SELECTdepartment_idFROMemployees);-- NOT EXISTS：查询没有员工的空部门SELECTdepartment_id,department_nameFROMdepartments dWHERENOTEXISTS(SELECT1FROMemployees eWHEREe.department_id=d.department_id);-- NOT EXISTS vs NOT IN的重要区别：-- NOT IN遇到子查询结果含NULL时返回空集-- NOT EXISTS不受NULL影响，通常更安全-- 实战：删除重复记录（保留rowid最大的行）DELETEFROMemployees e1WHEREEXISTS(SELECT1FROMemployees e2WHEREe2.employee_id=e1.employee_idANDe2.rowid>e1.rowid);ROLLBACK;-- 演示用，回滚

四、多列子查询

-- 多列比较：查询与某员工具有相同部门和职位的员工SELECTemployee_id,last_name,job_id,department_idFROMemployeesWHERE(job_id,department_id)IN(SELECTjob_id,department_idFROMemployeesWHEREemployee_id=141)ANDemployee_id<>141;-- 查询各部门薪资最高的员工（多列子查询的经典使用）SELECTe.employee_id,e.last_name,e.salary,e.department_idFROMemployees eWHERE(e.department_id,e.salary)IN(SELECTdepartment_id,MAX(salary)FROMemployeesGROUPBYdepartment_id)ORDERBYe.department_id;

五、相关子查询

相关子查询（Correlated Subquery）是指子查询引用了外层查询的列，外层每处理一行时，子查询都会重新执行一次。

-- 查询薪资高于本部门平均薪资的员工SELECTe1.employee_id,e1.last_name,e1.salary,e1.department_idFROMemployees e1WHEREe1.salary>(SELECTAVG(e2.salary)FROMemployees e2WHEREe2.department_id=e1.department_id-- 引用外层的department_id)ORDERBYe1.department_id,e1.salaryDESC;-- 查询薪资排名在本部门前30%的员工SELECTe1.employee_id,e1.last_name,e1.salary,e1.department_id,ROUND(e1.salary*100.0/(SELECTMAX(e2.salary)FROMemployees e2WHEREe2.department_id=e1.department_id),1)ASsalary_pct_of_maxFROMemployees e1WHEREe1.salary>=0.7*(SELECTMAX(e2.salary)FROMemployees e2WHEREe2.department_id=e1.department_id)ORDERBYe1.department_id,e1.salaryDESC;

六、内联视图（FROM子句中的子查询）

内联视图（Inline View）是在FROM子句中定义的子查询，可以像普通表一样使用。

-- 内联视图的基本使用SELECTdept_stats.department_id,dept_stats.avg_salary,e.last_name,e.salaryFROMemployees e,(SELECTdepartment_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment_id)dept_statsWHEREe.department_id=dept_stats.department_idANDe.salary>dept_stats.avg_salary;-- 使用WITH语句（CTE）：更清晰的内联视图WITHdept_avgAS(SELECTdepartment_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment_id),high_earnersAS(SELECTe.employee_id,e.last_name,e.salary,e.department_id,da.avg_salaryASdept_avg_salaryFROMemployees eJOINdept_avg daONe.department_id=da.department_idWHEREe.salary>da.avg_salary)SELECTh.employee_id,h.last_name,TO_CHAR(h.salary,'L99,999')ASsalary,TO_CHAR(h.dept_avg_salary,'L99,999')ASdept_avg,TO_CHAR(h.salary-h.dept_avg_salary,'L99,999')ASabove_avgFROMhigh_earners hORDERBYh.department_id,h.salaryDESC;

七、集合操作

集合操作将多个SELECT语句的结果合并，要求各SELECT语句的列数和数据类型兼容。

7.1 UNION（并集，去重）

-- UNION：合并结果集并去除重复行-- 查询在2005年之前或薪资超过12000的员工SELECTemployee_id,last_name,salary,hire_dateFROMemployeesWHEREsalary>12000UNIONSELECTemployee_id,last_name,salary,hire_dateFROMemployeesWHEREhire_date<TO_DATE('2005-01-01','YYYY-MM-DD');-- UNION自动去除两个集合中的重复行-- 注意：列名取第一个SELECT的列名-- 排序只能在最后一个SELECT后加ORDER BYSELECTemployee_id,last_name,salaryFROMemployeesWHEREsalary>12000UNIONSELECTemployee_id,last_name,salaryFROMemployeesWHEREdepartment_id=60ORDERBYsalaryDESC;-- ORDER BY放在最后

7.2 UNION ALL（并集，保留重复）

-- UNION ALL：合并结果集但保留重复行（性能比UNION好，省去去重步骤）-- 当确认两个结果集不会重复，或需要保留所有记录时使用-- 合并两个月的销售记录（允许重复，因为不同月份的记录逻辑上不重复）SELECT'Jan'ASmonth,order_id,amountFROMordersWHEREorder_month=1UNIONALLSELECT'Feb'ASmonth,order_id,amountFROMordersWHEREorder_month=2;-- 常见用法：同一查询分组产生小计和总计SELECTdepartment_id,job_id,COUNT(*)AScnt,SUM(salary)AStotalFROMemployeesGROUPBYdepartment_id,job_idUNIONALLSELECTdepartment_id,NULLASjob_id,COUNT(*)AScnt,SUM(salary)AStotalFROMemployeesGROUPBYdepartment_idUNIONALLSELECTNULL,NULL,COUNT(*),SUM(salary)FROMemployeesORDERBY1NULLSLAST,2NULLSLAST;-- 这种方式与ROLLUP等价，但可读性更差，推荐使用ROLLUP

7.3 INTERSECT（交集）

-- INTERSECT：返回两个查询结果的公共行（去重）-- 查询既在IT部门工作过，又是经理级别的员工SELECTemployee_idFROMemployeesWHEREdepartment_id=60INTERSECTSELECTmanager_idFROMdepartmentsWHEREmanager_idISNOTNULL;-- 实战：找出两张表中共同存在的数据（数据比对）-- 找出去年和今年都有订单的客户SELECTcustomer_idFROMordersWHEREEXTRACT(YEARFROMorder_date)=2024INTERSECTSELECTcustomer_idFROMordersWHEREEXTRACT(YEARFROMorder_date)=2023;

7.4 MINUS（差集）

-- MINUS：返回第一个查询结果中有、而第二个查询结果中没有的行-- 查询没有员工的部门SELECTdepartment_idFROMdepartments MINUSSELECTDISTINCTdepartment_idFROMemployeesWHEREdepartment_idISNOTNULL;-- 等价于NOT EXISTS写法（通常MINUS性能更好）SELECTdepartment_idFROMdepartments dWHERENOTEXISTS(SELECT1FROMemployees eWHEREe.department_id=d.department_id);-- 实战：数据一致性检查——找出源表有但目标表没有的记录SELECTemployee_id,last_nameFROMemployees_source MINUSSELECTemployee_id,last_nameFROMemployees_target;

7.5 集合操作的注意事项

-- 1. 列数必须相同SELECTemployee_id,last_nameFROMemployees-- 2列UNIONSELECTdepartment_idFROMdepartments;-- 1列 -- ERROR!-- 2. 对应列的数据类型必须兼容SELECTemployee_id,last_nameFROMemployees-- NUMBER, VARCHAR2UNIONSELECTdepartment_id,department_nameFROMdepartments;-- NUMBER, VARCHAR2 -- OK-- 3. 列名取第一个SELECT的列名SELECTemployee_idAS"编号",last_nameAS"姓名"FROMemployeesWHEREsalary>15000UNIONSELECTdepartment_id,department_nameFROMdepartmentsWHEREdepartment_id<30;-- 结果列名是"编号"和"姓名"，来自第一个SELECT-- 4. 集合操作可以链式组合SELECTemployee_idFROMemployeesWHEREsalary>12000UNIONSELECTemployee_idFROMemployeesWHEREdepartment_id=60MINUSSELECTemployee_idFROMemployeesWHEREhire_date>TO_DATE('2005-01-01','YYYY-MM-DD');

八、综合实战案例

案例一：复杂员工分析报表

-- 使用CTE构建复杂的员工分析报表WITHdept_summaryAS(SELECTdepartment_id,COUNT(*)AStotal_emp,ROUND(AVG(salary),2)ASavg_salary,MAX(salary)ASmax_salary,MIN(salary)ASmin_salaryFROMemployeesGROUPBYdepartment_id),emp_rankedAS(SELECTe.*,ds.avg_salaryASdept_avg,RANK()OVER(PARTITIONBYe.department_idORDERBYe.salaryDESC)ASsalary_rank,ROUND((e.salary-ds.avg_salary)*100.0/ds.avg_salary,1)ASpct_above_avgFROMemployees eJOINdept_summary dsONe.department_id=ds.department_id)SELECTer.employee_idAS"工号",er.last_nameAS"姓名",d.department_nameAS"部门",TO_CHAR(er.salary,'L99,999')AS"月薪",TO_CHAR(er.dept_avg,'L99,999')AS"部门均薪",er.salary_rankAS"部门排名",er.pct_above_avg||'%'AS"超均薪比例"FROMemp_ranked erJOINdepartments dONer.department_id=d.department_idWHEREer.salary_rank<=3ORDERBYer.department_id,er.salary_rank;

案例二：数据变更检测

-- 使用集合操作检测两个时间点的数据差异-- 模拟：找出员工信息的新增、删除和变更-- 新增员工（当前有，历史没有）SELECT'NEW'ASchange_type,employee_id,last_name,salaryFROMemployees_current MINUSSELECT'NEW',employee_id,last_name,salaryFROMemployees_historyUNIONALL-- 删除员工（历史有，当前没有）SELECT'DELETED'ASchange_type,employee_id,last_name,salaryFROMemployees_history MINUSSELECT'DELETED',employee_id,last_name,salaryFROMemployees_currentORDERBYchange_type,employee_id;

九、常见问题

Q1：子查询 vs 连接（JOIN），如何选择？

子查询：逻辑更清晰，适合"根据另一个表的条件过滤"的场景
连接（JOIN）：性能通常更好（可以利用连接优化），适合需要同时显示多表数据的场景
一般建议：如果查询需要显示两个表的数据列，用JOIN；如果只需要判断是否存在，用EXISTS

Q2：NOT IN中的NULL陷阱

-- 危险！如果子查询结果包含NULL，NOT IN会返回空结果集SELECTemployee_idFROMemployeesWHEREdepartment_idNOTIN(SELECTmanager_idFROMdepartments);-- 如果departments.manager_id有NULL，以上查询返回0行！-- 安全写法1：加WHERE过滤NULLSELECTemployee_idFROMemployeesWHEREdepartment_idNOTIN(SELECTmanager_idFROMdepartmentsWHEREmanager_idISNOTNULL);-- 安全写法2：使用NOT EXISTSSELECTemployee_idFROMemployees eWHERENOTEXISTS(SELECT1FROMdepartments dWHEREd.manager_id=e.department_id);