11：子查询、合并查询

1. 单行/多行子查询，in，all，any关键字：

• 嵌套select语句，也叫嵌套查询。

//显示SMITH同一部门的员工
select * from emp where deptno=(select deptno from emp where ename='SMITH');
​
//查询和10号部门的工作岗位相同的人员的名字，岗位，工资，部门号，但是不包含10自己
select ename,job,sal,deptno from emp where job in(select distinct job from emp where deptno=10) and deptno <> 10;
​
//显示工资比部门30的员工的最高工资高的员工的姓名、工资、部门号。
select ename,sal,deptno from emp where sal>(select max(sal) from emp where deptno=30); //聚合函数
select ename,sal,deptno from emp where sal>all(select sal from emp where deptno=30); //all关键字
​
//显示工资比部门30的任意员工的工资高的员工的姓名、工资、部门号。
select ename,sal,deptno from emp where sal>(select min(sal) from emp where deptno=30); //聚合函数
select ename,sal,deptno from emp where sal>any(select sal from emp where deptno=30); //any关键字

2. 多列子查询：

//查询和SMITH的部门和岗位完全相同的所有雇员，不包含SMITH本人
select * from emp where (deptno,job) in (select deptno,job from emp where ename='SMITH') and ename <> 'SMITH';

---

• 上面是where子句的子查询，作用是充当判断条件。

• 下面是from子句的子查询，作用是当作临时表。

---

// 显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
select avg(sal) from emp group by deptno; // 找到每个部门的平均工资
select ename,emp.deptno,sal,deptavg from emp,(select deptno,avg(sal) as deptavg from emp group by deptno) as tmp where emp.deptno=tmp.deptno and emp.sal>tmp.deptavg;

//查找每个部门工资最高的人的姓名，工资，部门，最高工资
select ename,sal,emp.deptno,maxsal from emp,(select deptno,max(sal) as maxsal from emp group by deptno) as tmp where tmp.deptno=emp.deptno and tmp.maxsal=emp.sal;

//显示每个部门的信息（部门名，编号，地址）和人员数量
select dept.dname,dept.deptno,dept.loc,tmp.deptnum from dept,(select deptno,count(*) as deptnum from emp group by deptno) as tmp where dept.deptno=tmp.deptno;

---

1. 合并查询：

• 在实际应用中，为了合并多个select的执行结果，可以使用集合操作符 union，union all

• union：该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

• union all：该操作符用于取得两个结果集的并集。当使用该操作符时，不会去掉结果集中的重复行。