数据库—子查询

子查询

我们可能会提出这样的问题，在雇员中谁的工资最高，或者谁的工资比SCOTT高。通过把一个查询的结果作为另一个查询的一部分，可以实现这样的查询功能。具体的讲：要查询工资高于SCOTT的雇员的名字和工资，必须通过两个步骤来完成，第一步查询雇员SCOTT的工资，第二步查询工资高于SCOTT的雇员。第一个查询可以作为第二个查询的一部分出现在第二个查询的条件中，这就是子查询。出现在其他查询中的查询称为子查询，包括其他查询的查询称为主查询。
子查询一般出现在SELECT语句的WHERE子句中。子查询比主查询先执行，结果作为主查询的条件，在书写上要用圆括号括起来，并放在比较运算符的右侧。子查询可以嵌套使用，最里层的查询最先执行。子查询可以在SELECT、INSERT、UPDATE、DELETE等语句中使用。
子查询按照返回数据的类型可以分为单行子查询、多行子查询和多列子查询。

一、单行子查询

1、包含一个子查询

例：查询比SCOTT（7788）工资高的雇员名字和工资。
SELECT ename,sal FROM emp WHERE sal>(SELECT sal FROM emp WHERE empno=7788);

2、包含两个子查询

例：查询和SCOTT （7788）同一部门且比他工资低的雇员名字和工资。
SELECT ename,sal FROM emp WHERE sal<(SELECT sal FROM emp WHERE empno=7788) AND deptno=(SELECT deptno FROM emp WHERE empno=7788);

二、多行子查询

如果子查询返回多行的结果，则我们称它为多行子查询。多行子查询要使用不同的比较运算符号，它们是IN、ANY和ALL。

1、IN类型----等于列表中的任意一个

例：查询部门20中职务同部门10的雇员一样的雇员信息。
SELECT empno,ename,job FROM emp WHERE job IN
(SELECT job FROM emp WHERE deptno=10) AND deptno=20;

2、ANY类型-----和子查询中返回的某一个值比较

例：查询工资低于某个“CLERK”的工资的雇员信息。
SELECT empno,ename,job,sal FROM empWHERE sal<ANY(SELECT sal FROM emp WHERE job=’CLERK’) AND job <> ’CLERK’;

3、ALL类型-----和子查询中返回的所有值比较

例：查询工资比所有的“SALESMAN”都高的雇员的编号、名字和工资。
SELECT empno,ename,sal FROM emp WHERE
sal>ALL(SELECT sal FROM emp WHERE job=’SALESMAN’);

三、多列子查询

如果子查询返回多列，则对应的比较条件中也应该出现多列，这种查询称为多列子查询。
例：查询职务和部门与SCOTT（7788）相同的雇员的信息。
SELECT empno,ename,sal FROM emp
WHERE (job,deptno) =(SELECT job,deptno FROM emp WHERE empno=7788);

四、在FROM从句中使用子查询

在FROM从句中也可以使用子查询，在原理上这与在WHERE条件中使用子查询类似。有的时候我们可能要求从雇员表中按照雇员出现的位置来检索雇员，很容易想到的是使用rownum虚列。
例：查询雇员表中排在第6~9位置上的雇员。
SELECT ename,sal FROM (SELECT rownum as num,ename,sal FROM emp WHERE rownum<=9)
WHERE num>=6;