1 # 查询底薪高于平均底薪的员工(WHERE子查询)
 2 SELECT ename,empno,sal FROM t_emp WHERE sal>=(SELECT AVG(sal) FROM t_emp);
 3 # SELECT 子查询，查询员工编号，姓名及部门名称
 4 SELECT e.empno,e.ename,(SELECT dname FROM t_dept WHERE deptno=e.deptno) FROM t_emp e;
 5 # 多行子查询FORD或者MARTIN两人的同事(同部门)
 6 SELECT ename,deptno FROM t_emp WHERE deptno IN (SELECT deptno FROM t_emp WHERE ename IN("FORD","MARTIN"))AND ename NOT IN ("FORD","MARTIN");
 7 # all，any关键字，前者代指结果集中的所有，后者代指结果集中任意一个
 8 SELECT ename,sal FROM t_emp WHERE sal>=ALL(SELECT sal FROM t_emp WHERE ename IN("FORD","MARTIN")) AND ename NOT IN("FORD","MARTIN");
 9 SELECT ename,sal FROM t_emp WHERE sal>=ANY(SELECT sal FROM t_emp WHERE ename IN("FORD","MARTIN")) AND ename NOT IN("FORD","MARTIN");
10 # 查询工资等级是3级或4级的员工信息EXISTS关键字
11 SELECT empno,ename,sal FROM t_emp WHERE EXISTS(SELECT grade FROM t_salgrade WHERE sal BETWEEN losal AND hisal AND grade IN (3,4));

where子查询，select子查询效率都极低，强烈不建议使用

from子查询中将结果集作为临时表，采用表连接的方式来使用效率是比较高的，推荐使用

版权声明：本文为fly10086原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://www.cnblogs.com/fly10086/p/12500355.html