Oracle学习——子查询
本篇文章将介绍SQL中子查询的内容。
1、子查询
子查询是一个SELECT语句,它是嵌入在另一个SELECT语句中的子句。放置一个查询到另一个查询中,内查询或子查询返回一个值给外查询或主查询。使用子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。
语法结构:
1 2 3 4 | SELECT select_list FROM table WHERE expr ( SELECT select_list FROM table ) |
- 子查询会在主查询之前执行一次(只会执行一次)
- 子查询的结果会被用于主查询
子查询语句可以放在许多的SQL子句中。包括:WHERE、HAVING、FROM子句中
2、使用子查询
2.1、使用原则
- 子查询放在比较条件的右边
- 在单行子查询(返回的结果集是一个)中用单行运算符(=,>,<),在多行子查询中(返回的结果是多行)使用多行运算符(ANY,ALL...)。
示例:在employees表中找出比Abel工资高的人。
1 2 3 4 5 6 | SELECT em. name , em.salary FROM employees em WHERE em.salary >( SELECT m.salary FROM employees m WHERE m. name = 'Abel' ) |
3、子查询的类型
单行子查询:返回的子查询结果只有一条数据返回。
多行子查询:返回的子查询结果有不止一条数据返回。
3.1、单行子查询
主查询对于子查询结果的单行比较运算符:
- =:等于
- >,>=:大于,大于等于
- <,<=:小于,小于等于
示例:显示jobID与雇员141(主键employeeID)相同的雇员名字和jobID。
1 2 3 4 5 6 | SELECT em.jobID, em. name FROM employees em WHERE em.jobID =( SELECT jobID FROM employees e WHERE e.employeeID=141 ) |
3.2、单行子查询中使用组函数
子查询中是可以使用组函数进行分析的。例如下面这示例:显示所有薪水=最低薪水的雇员name和jobID。
1 2 3 4 5 | SELECT em.jobID, em. name FROM employees em WHERE em.salary =( SELECT MIN (salary) FROM employees ) |
3.3、HAVING子句中使用单行子查询
在HAVING子句中可以使用子查询。
示例:显示所有最低薪水小于 部门ID为50的最低薪水 的部门号与最低薪水。
1 2 3 4 5 6 7 | SELECT em.department_id, MIN (em.salary) FROM employees em GROUP BY em.department_id HAVING MIN (em.salary) > ( SELECT MIN (e.salary) FROM employees e WHERE e.department_id=50 ) |
3.5、多行子查询
主查询对子查询的多行比较运算符。
- IN:等于集合中的任何值,只能做相等判断。
- ANY:比较子查询返回的每个值 (OR),可以做大于或者小于判断。
- ALL:比较子查询返回的全部值 (AND),可以做大于或者小于判断。
3.6、多行子查询中使用IN
示例:查找各部门收入为部门最低的那些雇员,显示他们的名字、薪水、部门ID
分析;先需要找出每个部门中收入最低的雇员的name或者employee_id。
1 2 3 4 | SELECT e.employeeID FROM employees e, ( SELECT MIN (salary) mins, department_id FROM employees GROUP BY department_id) av WHERE e.department_id=av.department_id AND e.salary=av.mins |
再通过IN在主查询中,找到这些employee_id的雇员名称和薪水,部门id。
1 2 3 4 5 6 7 8 9 10 | SELECT em.department_id, em. name , em.salary FROM employees em WHERE em.employeeID in ( SELECT e.employeeID FROM employees e, ( SELECT MIN (salary) mins, department_id FROM employees GROUP BY department_id ) av WHERE e.department_id=av.department_id AND e.salary=av.mins ) |
3.7、多行子查询中使用ANY
语法结构:
1 2 3 4 | SELECT select_list FROM table WHERE salary < ANY ( SELECT select_list FROM table ) |
其中<ANY表示小于最大值的即可,>ANY表示大于最小值即可。
示例:显示工作岗位不是IT_PROG的雇员,并且这些雇员的薪水少于IT_PROG工作岗位的职员,显示这些职员的姓名,薪水。
1 2 3 4 5 6 | select e. name , e.salary from employees e where e.salary < ANY ( SELECT em.salary FROM employees em WHERE em.job_id= 'IT_PROG' ) and e.job_id <> 'IT_PROG' ; |
3.8、多行子查询中使用ALL
语法上与ANY一致,只用把ANY位置转化为ALL即可。其中<ALL表示小于最小值的即可,>ALL表示大于最大值即可。
示例:显示那些薪水低于工作岗位是“IT_PROG”的最低薪水,并且工作岗位不是IT_PROG的所有雇员,显示这些雇员的姓名,薪水。
1 2 3 4 5 6 | SELECT e. name , e.id FROM employees e WHERE e.salary < ALL ( SELECT em.salary FROM employees em WHERE em.job_id= 'IT_PROG' ) AND e.job_id <> 'IT_PROG' ; |
4、习题
示例1:显示与Zlotkey在同一部门的雇员name,并且结果中不包含有Zlotkey
1 2 3 4 5 6 | SELECT e. name , e.id FROM employees e WHERE e.department_id = ( SELECT em.department_id FROM employees em WHERE em. name = 'Zlotkey' ) AND e. name <> 'Zlotkey' ; |
示例2:创建一个查询显示所有其薪水高于平均薪水的雇员的雇员号和名字。
1 2 3 4 5 6 | SELECT e. name , e.id FROM employees e WHERE e.salary > ( SELECT AVG (em.salary) FROM employees em ) ORDER BY e.salary |
示例3:所有部门中有些部门的雇员名字中包含u,显示所有工作在这些部门的雇员名字和id。
1 2 3 4 5 6 | SELECT e. name , e.id FROM employees e WHERE e.department_id IN ( SELECT em.department_id FROM employees em WHERE em. name LIKE '%u%' ) |
示例4:显示所有部门地点号(department location ID)是1700的雇员的name、department和jobID。(部门地点号在department表中)
1 2 3 4 5 6 | SELECT e. name , e.job_id, e.department_id FROM employees e WHERE e.department_id IN ( SELECT de.department_id FROM department de WHERE de.location_id = 1700 ) |
示例5:显示经理是King的雇员的名字和薪水。
1 2 3 4 5 6 | SELECT e. name , e.salary FROM employees e WHERE e.manager_id IN ( SELECT em.employee_id FROM employees em WHERE em. name = 'King' ) |
这里采用IN是因为防止King有多个人。
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· CSnakes vs Python.NET:高效嵌入与灵活互通的跨语言方案对比
· DeepSeek “源神”启动!「GitHub 热点速览」
· 我与微信审核的“相爱相杀”看个人小程序副业
· Plotly.NET 一个为 .NET 打造的强大开源交互式图表库
· 上周热点回顾(2.17-2.23)