定义
子查询(Sub Query)又叫(Inner Query),嵌套查询(Nested Query)，常常是嵌套在其他SQL查询的WEHERE子句中的查询，用于为主查询返回其所需要的数据，或对检索数据进行进一步限制。
子查询可以应用在FROM、WHERE、HAVING子句中，也可在SELECT、INSERT、UPDATE、DELETE语句中，和=、<、>、>=、<=、IN、BETWEEN等运算符号一起使用
SELECT中的子查询
查看emp表
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 |
查询emp表中谁的工资比BLAKE多
SQL> select * from emp where sal>( select * from emp where ename='BLAKE' );
select * from emp where sal>(select * from emp where ename='BLAKE')
*
ERROR at line 1:
ORA-00913: too many values |
报错了
子查询的SELECT子句中只能有一个列（如果主查询中有多个列用于与子查询选中的列相比较时除外）
SQL> select * from emp where sal>( select sal from emp where ename='BLAKE' );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 |
在圆括号中查询得到一个唯一结果，把它作为外层查询的一个参数，因此子查询只能在圆括号中
查询和MARTIN在同一部门且工资比他多的员工信息
SQL> select * from emp where deptno=( select deptno from emp where ename='MARTIN' ) and sal>( select sal from emp where ename='MARTIN' );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 |
这也属于单行子查询
查询那个员工的工资最多（不用考虑并列最多的问题，因为最大值只有一种）
SQL> select * from emp where sal=(select max(sal) from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 |
这依然数据单行子查询
那如果子查询中没有返回数据时呢
查询比KING奖金多的员工信息
SQL> select * from emp where comm>(select comm from emp where ename='KING');
no rows selected |
怎么没有信息？
当子查询没有得到结果时，其返回值为 null，外层的查询中的 where 条件 comm>null，则 where 不成立，则没有数据返回 comm 存在 null 值，那么在比较两个 null 值结果依然是 null，所以还是没 有数据返回
那么怎么才能查出来呢，用nvl将空值转换成0即可
SQL> select * from emp where comm>(select nvl(comm,0)from emp where ename='KING');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 |
看来只有做销售才能有奖金。
FROM中的子查询
查询NEW YOEK
部门工作的员工信息和个数
SQL> select * from ( select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno ) where loc='NEW YORK';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DNAME LOC
------ ------ --------- ----- ------------------- ----- ---- ---------- --------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 ACCOUNTING NEW YORK
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 ACCOUNTING NEW YORK
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 ACCOUNTING NEW YORK
SQL> select count(*) from ( select e.empno,e.ename,e.job,e.mgr,e.hiredate,e.sal,e.comm,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno ) where loc='NEW YORK';
COUNT(*)
----------
3 |
HAVING中的子查询
查询比30号部门最低工资还低工资低的部门编号
SQL> select deptno from emp group by deptno having min(sal)>( select min(sal) from emp where deptno=30 );
DEPTNO
-------
10 |
查询那个部门的平均工资最高
SQL> select dname from dept where deptno=( select deptno from emp group by deptno having avg(sal)=( select max(avg(sal)) from emp group by deptno ) );
DNAME
----------
ACCOUNTING |
INSERT中的子查询
insert中的子查询，用于将子查询的数据插入到其他表中，子查询的数据可以被任何字符、日期或者函数所修饰
创建表tb_ins01定义参照emp数据不要
SQL> create table tb_ins01 as select * from emp where 1=2;
Table created.
SQL> select * from tb_ins01;
no rows selected
SQL> desc tb_ins01;
Name Null? Type
----------------------- -------- ----------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2) |
将emp中在NEW YORK工作的员工信息复制到tb_ins01中
SQL>
insert into tb_ins01 select empno,ename,job,mgr,hiredate,sal,comm,deptno from ( select empno,ename,job,mgr,hiredate,sal,comm,e.deptno,loc from emp e,dept d where e.deptno=d.deptno ) where loc='NEW YORK';
3 rows created.
SQL> select * from tb_ins01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ------ --------- ----- ------------------- ----- ---- -------
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 |
UPDATE中的子查询
创建emp表的复制tb_upd01
将emp表中1982年之前出生的员工工资增加1.2倍更新到 tb_upd01中
SQL> create table tb_upd01 as select * from emp;
Table created.
SQL> select * from tb_upd01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ------ --------- ----- ------------------- ----- ---- -------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
SQL> update tb_upd01 set sal=sal*1.2 where empno in (select empno from emp where hiredate<to_date('1982-01-01'));
11 rows updated.
SQL> select * from tb_upd01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 960 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1920 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1500 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 3570 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1500 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 3420 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2940 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 6000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1800 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 1140 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3600 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected. |
DELETE语句中的子查询
将emp表中1982年之前出生的员工在tb_upd01表中删除（涨完工资就开除！！）
SQL> delete from tb_upd01 where empno in (select empno from emp where hiredate<to_date('1982-01-01'));
11 rows deleted.
SQL> select * from tb_upd01;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 |
多行子查询
其实刚才的例子里已经有了，就是子查询返回的值有多个
查看每个部门工资最少的员工信息
SQL> select * from emp where sal=(select min(sal) from emp group by deptno);
select * from emp where sal=(select min(sal) from emp group by deptno)
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row |
为什么会报错，因为子查询中的返回值有多个无法与主查询一一匹配
因此对于多行子查询需要应用多行运算符（IN、ALL、ANY）
IN：表示外层条件数据集合中的任意成员
ANY：表示外层条件只满足子查询的任意一个值即可
ALL：表示外层条件必须满足子查询的全部值
IN
SQL> select * from emp where sal in (select min(sal) from emp group by deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 |
ALL
查询工资比MANAGER工资还多并且工作岗位不是MANAGER的员工信息
SQL> select * from emp where sal >all(select sal from emp where job='MANAGER');
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10< |
sal < all() 表示sal小于其中的最小值
sal > all() 表示sal大于其中的最大值
ANY
查询比某个部门的最高工资还多的员工信息
SQL> select * from emp where sal>any(select max(sal) from emp group by deptno);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------ ---------- --------- ----- ------------------- ----- ----- -------
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 |
规则
子查询必须在圆括号中
子查询不能使用ORDER BY，但是可以使用GROUP BY起来起到相同的作用
返回多行数据的子查询只能同多值操作符号一同使用，如IN
子查询的SELECT列表中不能包含任何对BLOB、ARRAY、CLOB、NCLOB值的引用
子查询不能直接用在集合函数中
BETWEEN不能和子查询一同使用，但是可以用在子查询中
