数据库-子查询
概念:
查询条件中的嵌套查询
查询工资最高的员工信息
– 1.查询最高工资是多少
select max(salary) from emp;
– 2.查询员工信息,并且工资等于9000的
select * from emp where emp.salary = 9000;
通过子查询实现
select * from emp where emp.salary = (select max(salary) from emp);
- 子查询的结果是单行单列的:<,>,<=,>=,=
select * from emp where emp.salary = (select max(salary) from emp);
– 查询员工工资小于平均工资的人
select * from emp where emp.salary < (select avg(salary) from emp); - 子查询的结果是多行单列的:in
– 查询财务部和市场部所有员工信息
select id from dept where name = ‘财务部’ or name =‘市场部’;
select * from dept where dept_id = 3 or dept_id =2
– 子查询
select * from dept where dept_id in (2,3);
select * from dept where dept_id in (select id from dept where name = ‘财务部’ or name =‘市场部’;); - 子查询的结果是多行多列的:子查询可以做为一张虚拟表
– 查询员工入职日期是2011-11-11日之后入职的员工信息和部门信息
select * from dept t1 ,(select * from emp where emp.join_date > ‘2011-11-11’) t2 where t1.id=t2.dept_id;