MySQL -多表查询 联合查询、子查询

联合查询

联合查询 union把多次查询的结果合并输出为一个新的查询结果集并且去重，如果有员工同时符合两个条件，只出现一次。
union all把多次查询的结果合并输出为一个新的查询结果集，不去重，如果有员工同时符合两个条件，会出现两次
联合查询多张表的列数必须一致，字段类型也需要一致

select * from emp where salary<11
union
select *from emp where age>40;

子查询

子查询（嵌套查询） 外部第一层可以是增删改查
子查询的位置：where之后，from之后，select之后
子查询包含：标量子查询（查询结果为单个值），列子查询（查询结果为一列），行子查询（查询结果为一行），表子查询（查询结果为多行多列）

标量子查询

2-查询刑侦部的员工信息

3-查询比蒋峰的入职时间晚的员工

select dept.id from dept where name='刑侦部';
select * from emp where dept_id=(select dept.id from dept where name='刑侦部');
select * from emp where entrydate>(select entrydate from emp where name='蒋峰');

列子查询

常用操作符：
in 在指定集合范围内多选一 
not in不在指定范围内 
any/some 子查询返回列表中，有一个满足条件即可 
all：子查询返回列表中，所有值都满足

查询部门为刑侦部或法医部的员工信息
select emp.* from emp 
where 
dept_id in (select dept.id from dept where dept.name='刑侦部' or dept.name='法医部') ;

查询部门为刑侦部的员工工资
select salary from emp 
where 
emp.dept_id =(select dept.id from dept where dept.name='刑侦部');

查询工资超过 刑侦部所有员工工资的 员工
select * from emp 
where 
salary > all (select salary from emp where emp.dept_id =(select dept.id from dept where dept.name='刑侦部'));

查询技术部员工的工资
select emp.salary from emp 
where 
emp.dept_id=(select id from dept where dept.name='技术部');

查询工资大于任一技术部员工的其他员工信息，且不包括技术部员工
select * from emp 
where 
salary > any(select emp.salary from emp where emp.dept_id=(select id from dept where dept.name='技术部')) and dept_id != (select dept.id from dept where dept.name='技术部') or dept_id is null;

行子查询

查询部门和薪资与杜城相同的员工，且不为杜城本人。两种实现方法

select * from emp where 
salary=(select emp.salary from emp where name='杜城') 
and managerid=(select managerid from emp where name='杜城') 
and name!='杜城';

select * from emp where 
(salary,managerid) = (select salary,managerid from emp where name='杜城') 
and name!='杜城' ;

表子查询

查询杜城和沈翊的薪资与部门id
select emp.dept_id ,emp.salary from emp 
where 
name = '杜城' or name = '沈翊';

查询薪资和部门与杜城和沈翊相同的员工信息
select * from emp where 
(dept_id,salary) in (select emp.dept_id ,emp.salary from emp where name = '杜城' or name = '沈翊');


查询入职时间晚于2009-01-01的员工信息
select * from emp where entrydate > '2009-01-01' ;

查询入职时间晚于2009-01-01的员工信息 以及部门信息
select e.*,dept.* from (select * from emp where entrydate > '2009-01-01' ) e left join dept on e.dept_id = dept.id;