Oracle教程FG055-SQL子查询

1. 子查询基础

子查询是嵌套在其他SQL语句中的SELECT语句，用于返回数据给外部查询。子查询可以在SELECT、INSERT、UPDATE、DELETE语句中使用，是SQL中强大的功能之一。更多学习教程www.fgedu.net.cn

子查询类型：单行子查询、多行子查询、相关子查询、嵌套子查询、EXISTS子查询。

2. 单行子查询

单行子查询返回单个值，通常与单行比较运算符（=, >, <, >=, <=, <>）一起使用。

— 查询工资高于平均工资的员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME SALARY
———– ——————– ————————- ———-
100 Steven King 24000
101 Neena Kochhar 17000
102 Lex De Haan 17000

— 查询与Lex De Haan同一部门的员工
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM employees WHERE last_name = ‘De Haan’ AND first_name = ‘Lex’);EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID
———– ——————– ————————- ————-
100 Steven King 90
101 Neena Kochhar 90
102 Lex De Haan 90

3. 多行子查询

多行子查询返回多个值，通常与多行比较运算符（IN, ANY, ALL）一起使用。

— 查询在IT或Executive部门工作的员工
SELECT employee_id, first_name, last_name, department_id
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE department_name IN (‘IT’, ‘Executive’));EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID
———– ——————– ————————- ————-
100 Steven King 90
101 Neena Kochhar 90
102 Lex De Haan 90
103 Alexander Hunold 60
104 Bruce Ernst 60

— 查询工资高于IT部门所有员工的员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 60);EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME SALARY
———– ——————– ————————- ———-
100 Steven King 24000
101 Neena Kochhar 17000
102 Lex De Haan 17000

— 查询工资高于IT部门任何员工的员工
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE salary > ANY (SELECT salary FROM employees WHERE department_id = 60);EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME SALARY
———– ——————– ————————- ———-
100 Steven King 24000
101 Neena Kochhar 17000
102 Lex De Haan 17000
103 Alexander Hunold 9000

4. 相关子查询

相关子查询使用外部查询中的列，对外部查询的每一行执行一次。

— 查询每个部门中工资高于该部门平均工资的员工
SELECT e.employee_id, e.first_name, e.last_name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id);EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID SALARY
———– ——————– ————————- ————- ———-
100 Steven King 90 24000
103 Alexander Hunold 60 9000

— 查询有下属的员工（作为经理的员工）
SELECT e.employee_id, e.first_name, e.last_name
FROM employees e
WHERE EXISTS (SELECT 1 FROM employees WHERE manager_id = e.employee_id);EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME
———– ——————– ————————-
100 Steven King
102 Lex De Haan
103 Alexander Hunold

5. 嵌套子查询

嵌套子查询是指一个子查询中包含另一个子查询，形成多层嵌套结构。

— 查询工资高于平均工资的员工，且这些员工所在部门的平均工资高于公司平均工资
SELECT e.employee_id, e.first_name, e.last_name, e.department_id, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees)
AND e.department_id IN (
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees)
);EMPLOYEE_ID EMP_NAME EMP_EMP_EMP_LAST_NAME DEPARTMENT_ID SALARY
———– ——————– ————————- ————- ———-
100 Steven King 90 24000
101 Neena Kochhar 90 17000
102 Lex De Haan 90 17000

6. EXISTS子查询

EXISTS子查询用于检查子查询是否返回任何行，返回布尔值（TRUE或FALSE）。

— 查询存在员工的部门
SELECT department_id, department_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees WHERE department_id = d.department_id);DEPARTMENT_ID DEPARTMENT_NAME
————- ——————————
60 IT
90 Executive

— 查询不存在员工的部门
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees WHERE department_id = d.department_id);DEPARTMENT_ID DEPARTMENT_NAME
————- ——————————
100 Finance

7. FROM子句中的子查询

在FROM子句中使用子查询，将子查询的结果作为一个临时表。

— 使用子查询作为临时表
SELECT d.department_name, avg_salary
FROM (
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
) e
JOIN departments d ON e.department_id = d.department_id;DEPARTMENT_NAME AVG_SALARY
—————————— ———–
IT 7500
Executive 19333.3333333333333333333333333333333

— 使用子查询和别名
SELECT dept_name, max_salary
FROM (
SELECT d.department_name AS dept_name, MAX(e.salary) AS max_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name
) t
WHERE max_salary > 10000;DEPT_NAME MAX_SALARY
—————————— ———-
Executive 24000

8. 最佳实践

风哥提示：SQL子查询的最佳实践：
1. 合理使用子查询，提高查询的可读性和维护性
2. 对于复杂查询，考虑使用子查询分解复杂逻辑
3. 避免使用过多的嵌套子查询，影响性能
4. 对于大型表，考虑使用物化视图或临时表替代复杂子查询
5. 合理使用EXISTS和IN子查询，根据具体情况选择
6. 为子查询中的列创建索引，提高子查询性能
7. 避免在子查询中使用ORDER BY子句，除非必要
8. 对于相关子查询，考虑使用JOIN替代，提高性能
9. 定期分析子查询性能，优化执行计划
10. 遵循SQL编码规范，保持子查询的可读性

生产环境建议：在生产环境中，应尽量简化子查询的复杂度，避免过多的嵌套层次。对于大型表，建议为子查询中的列创建索引，并考虑使用JOIN操作替代相关子查询，以提高查询性能。同时，定期分析执行计划，优化子查询策略。

更多视频教程www.fgedu.net.cn

学习交流加群风哥微信: itpux-com

学习交流加群风哥QQ113257174

更多学习教程公众号风哥教程itpux_com

from oracle:www.itpux.com

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html