Oracle - 子查询

Oracle - 子查询

• 主查询，子查询：至少有2次查询

一、单行单列子查询

• 行和列都是一个

--查询工资大于平均工资的员工
select * from employees where salary > (
	select avg(salary) from employees
	);

--查询工资大于每个部门的平均工资的员工
select * from employees where salary > (
	select max(avg(salary)) from employees 
	where department_id is not null group by department_id 
	);

二、多行单列子查询

1.All：与子查询返回的每一个值比较

• **>all：**指大于子查询数据中的最大值
• **<all：**指小于子查询数据中的最小值

--查询工资大于每个部门的平均工资的员工
select * from employees where salary > all (
	select avg(salary) from employees where department_id is not null group by department_id	
)

2.Any：与子查询返回的任意一个值比较

• **>any：**指大于子查询数据中的最小值
• **<any：**指小于子查询数据中的最大值

--查询工资大于任意一个部门的平均工资的员工
select * from employees where salary > any(
	select avg(salary) from employees where department_id is not null group by department_id	
)

3.In：与列表中的任意一个值相等

• or xxx=? or xxx=?

--查询工资等于任意一个部门平均工资的员工
select * from employees where salary in (
	select avg(salary) from employees where department_id is not null group by department_id							
)

三、多行多列子查询

--查询每个部门工资最低的员工
select * from employees where (department_id ,salary) in (
	select (department_id ,min(salary)) from employees where department_id is not null group by department_id	
)