多表查询|子查询

行子查询

查询与张无忌工资相同, 且直属领导相同的员工

#写法1
select * from emp
    where salary = (select salary from emp where name='张无忌')
    and managerId = (select managerId from emp where name='张无忌');
#可以合并起来,写入一个集合
select * from emp
    where (salary, managerId) = (select salary,managerId from emp where name = '张无忌');

表子查询

子查询返回的结果是一张表, 多行多列

查询与杨逍或小白薪资和职位相同的员工

#据说数据库调优中能不用in就不用in
select * from emp
    where (salary, job) in (select salary,job from emp where name in ('杨逍','小白'));

查询在2004-05-24之后入职的对应的员工和部门信息

#where语句的书写位置
#用表子查询获取的表代替emp
select *
from (select * from emp where entryDate>'2004-05-24') e
left join dept d
on e.dept_id = d.id;