# 子查询是一种查询中嵌套查询的语句
# 但非常不推荐,所有子查询的效率会非常低,如果一定要用只用from子查询
# 查询底薪高于平均底薪的员工
# WHERE子查询(不推荐)
SELECT empno,ename,sal
FROM t_emp
WHERE sal>= (SELECT AVG(sal) FROM t_emp); # 生成了一个值再比较
# SELECT子查询(不推荐)
SELECT e.empno,e.ename,
(SELECT dname FROM t_dept WHERE deptno=e.deptno) as "部门名称"
FROM t_emp e;
# 如果一定要用子查询,推荐使用FROM子查询
SELECT e.empno,e.ename,e.sal,t.avg
FROM t_emp e JOIN
(SELECT deptno,AVG(sal) as avg FROM t_emp GROUP BY deptno) t
ON e.deptno=t.deptno AND e.sal>=t.avg;
# 如何用子查询找FORD和MARTIN两个人的同事
SELECT ename
FROM t_emp
WHERE deptno IN # 如果t_emp表中deptno字段在子集合中,则就是我们要找的名单
(SELECT deptno FROM t_emp WHERE ename IN ("FORD","MARTIN")) # 子查询:先找出ford和martin的部门编号
AND ename NOT IN ("FORD","MARTIN") # 显示的名单不能出现Ford和Martin
# 查询比ford和Martin底薪都高的员工信息
SELECT ename,sal
FROM t_emp
WHERE sal >= ALL # ALL表示所有子集的结果
(SELECT sal
FROM t_emp WHERE ename IN ("FORD","MARTIN"))
AND ename NOT IN ("FORD","MARTIN")
SELECT ename,sal
FROM t_emp
WHERE sal >= ANY # ANY代表子集的任何一个结果
(SELECT sal
FROM t_emp WHERE ename IN ("FORD","MARTIN"))
AND ename NOT IN ("FORD","MARTIN")
# 查询工资等级是3级或4级的员工信息
SELECT ename,sal
FROM t_emp
WHERE EXISTS(
SELECT *
FROM t_salgrade
WHERE sal BETWEEN losal and hisal
AND grade IN(3,4)
);