mysql子查询

子查询

# 谁的工资比Abel高?
# 方式1(自连接)
select e2.last_name,e2.salary from employees e1,employees e2
where e2.salary > e1.salary and e1.last_name = 'Abel';
# 方式2(子查询)
select last_name,salary from employees
where salary > (
	select salary
	from employees
	where last_name = 'Abel'
);
# 分类
# 返回一条记录的子查询:单行子查询(比较时使用>,<,=)

# eg.返回工资最少的员工的信息
select last_name,job_id,salary from employees
where salary = (
	select min(salary) from employees
);
# eg.job_id与141号员工相同,salary比143号员工多的员工信息
select last_name,job_id,salary from employees
where job_id = (
	select job_id from employees where employee_id = 141
) and salary > (
	select salary from employees where employee_id = 143
);
# 返回多条记录的子查询:多行子查询(比较时使用in/not in,any,all)

# 薪资小于任意一个job_id为'IT_PROG'的员工的薪资,且job_id不是'IT_PROG'的员工
# < any指比任意一个小即可
select employee_id,last_name,job_id,salary from employees
where job_id != 'IT_PROG'
and salary < any (
	select salary from employees where job_id = 'IT_PROG'
);
# 多行子查询要注意,子查询的结果中要避免出现null的项.

-- Attention   有时必须要起别名
select max(p) from (select avg(speed) p  from V_test group by maker) linshi;		// OK
select max(p) from (select avg(speed) p from V_test group by maker);				// Error

select maker,count(*) from (select * from V_test where price > 1000) linshibiao  group by maker;	// OK
select maker,count(*) from (select * from V_test where price > 1000) group by maker;		// Error

子查询必须放在圆括号内。

相关子查询

子查询的执行依赖于外部查询。
每执行一次外部查询，子查询都需要重新计算一次。

# 薪资大于本部门平均薪资的员工信息
select last_name,salary,department_id
from employees e1
where salary > (
	select avg(salary) from employees e2
	where department_id  = e1.department_id
);
# 员工信息按照department_name排序
select employee_id,salary,department_id from employees order by department_id;
select employee_id,salary from employees e
order by (
	select department_name
	from departments d
	where e.department_id = d.department_id
) desc;
# 查询管理者的信息
# 方式1
select distinct mgr.employee_id,mgr.last_name,mgr.job_id
from employees mgr
inner join employees emp on mgr.employee_id = emp.manager_id;
# 方式2
select employee_id,last_name,job_id
from employees
where employee_id in (
	select distinct manager_id from employees
);
# 方式3(根据exists的true\false来过滤外查询的结果集)
select employee_id,last_name,job_id
from employees e1
where exists (
	select * from employees e2 where e1.employee_id = e2.manager_id
);

exists
子查询中存在记录返回true否则false
in
指定项是否在集合中存在

SQL中exists和in的区别
自连接的效率大于子查询