Oracle SQL子查询笔记

 

    子查询是指嵌入在其他SQL语句中的SELECT语句，也称为嵌套查询。注意，当在DDL语句中引用子查询时，可以带有Order By子句；但是当在where子句、Set子句中引用子查询时，不能带有Order by 子句。子查询具有以下一些作用：

    1、通过在insert或create table语句中使用子查询，可以将源表数据插入目标表中。

    2、通过在create view或create materialieed view中使用子查询，可以定义视图或实体化视图所对应的select语句。

    3、通过在update语句中使用子查询可以修改一列或多列数据。

    4、通过在where、having、start with子句中使用子查询，可以提供条件值。

    根据子查询返回结果的不同，子查询又被分为单行子查询、多行子查询和多列子查询。

    一、单行子查询

    单行子查询是指只返回一行数据的子查询语句。当在where子句中引用单行子查询时，可以使用单行比较符(=、>、<、>=、<=、<>)。

    例：显示JONES同部门的姓名、工资和部门。

     sql代码：

     SELECT ename,sal,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='JONES');

    二、多行子查询

    多行子查询是指返回多行数据的子查询语句。当在where子句中使用多行子查询时必须要使用多行比较符(IN、ALL、ANY)。它们的作用如下： 

    IN：匹配于子查询结果的任一个值即可。

    ALL：必须要符合子查询结果的所有值。

    ANY：只要符合子查询结果的任一个值即可。

    注意，ALL和ANY操作符不能单独使用，而只能与单行比较符(=，>，<，>=，<=，<>)结合使用。

    1、在多行子查询中使用IN操作符

    当在多行子查询中使用IN操作符时，会处理匹配于子查询任一值的行。

    例：显示与部门10所有员工同工作的雇员名、岗位、工资、部门号的员工。

    sql代码:

    SELECT ename,job,sal,deptno FROM emp WHERE job IN (SELECT distinct job FROM emp WHERE deptno=10);

    2、在多行子查询中使用ALL操作符

    ALL操作符必须与单行操作符结合使用，并且返回行必须要匹配于所有子查询结果。

    例：显示高于部门30所有雇员工资的雇员名、工资和部门号。

    sql代码：

    SELECT ename,sal,deptno FROM emp WHERE sal > all (SELECT sal FROM emp where deptno=30);

    3、在多行子查询中使用ANY操作符

    ANY操作符必须与单行操作符结合使用，并且返回行只需匹配于子查询的任一个结果即可。

    例：显示高于部门30的任意雇员工资的雇员名、工资和部门号。

    sql代码：

    SELECT ename,sal,deptno FROM emp where sal > ANY (SELECT sal FROM emp WHERE deptno=30);

    三、多列子查询

    单行子查询是指子查询只返回单列单行数据，多行子查询是指子查询返回单列多行数据，二者都是针对单列而言的。而多列子查询则是指返回多列数据的子查询语句。当多列子查询返回单行数据时，在where子句中可以使用单行比较符；当多列子查询返回多行数据时，在where子句中必须使用多行比较符(IN、ANY、ALL)。

    例：显示与SMITH部门和岗位完全相同的所有雇员的雇员名，工作岗位、工资和部门号。

    SELECT ename,job,sal,deptno FROM emp WHERE (deptno,job) = (SELECT deptno,job FROM emp WHERE ename='SMITH');

 

    在使用子查询比较多个列的数据时，即可以使用成对比较，也可以使用非成对比较。其中，成对比较要求多个列的数据必须同时匹配，而非成对比较则不要求多个列的数据同时匹配。

    如下图：

    如图所示，当进行成对比较时，要求工资和补助必须同时匹配；而当执行非成对比较时，只要工资匹配于工资列表中的某一个、补助匹配于补助列表中的某一个就可以了。

    1、成对比较示例

     当执行成对比较时，因为要求多个列的数据必须同时匹配，所以必须要使用多列子查询实现。

    例：显示工资和补助与部门30雇员的工资和补助完全匹配的所有雇员。 

    sql代码：

    SELECT ename,sal,comm,deptno FROM emp  WHERE (sal,nvl(comm,-1)) IN (SELECT sal,nvl(comm,-1) FROM emp WHERE deptno=30);

    2、非成对比较示例

    执行非成对比较时，应该要使用多个多行子查询来实现。

    例：显示工资匹配于部门30工资列表、补助匹配于部门30补助列表的所有雇员。

    sql代码：

    SELECT ename,sal,comm,deptno FROM emp WHERE sal IN (SELECT sal FROM emp WHERE deptno=30) AND nvl(comm,-1) IN (select nvl(comm,-1) FROM emp WHERE deptno=30);

    四、其他子查询

    在where子句中除了可以使用单行子查询、多行子查询以及多列子查询外，还可以用相关子查询。另外在FORM子句、DML语句、DDL语句中也可以使用子查询。

    1、相关子查询

    相关子查询是指需要引用主查询表列的子查询语句，相关子查询是通过exists谓词来实现的。

    例：显示工作在"NEW YORK"的所有雇员。

    sql代码：

    SELECT ename,job,sal,deptno FROM emp WHERE EXISTS (SELECT 1 FROM dept WHERE dept.deptno=emp.deptno AND dept.loc='NEW YORK');

    2、在FROM子句中使用子查询

    当在FORM子句中使用子查询时，该子查询会被作为视图对待，因此也被称为内嵌视图。注意，当在FORM子句中使用子查询时，必须给子查询指定别名。  

    例：显示高于部门平均工资的雇员信息。

    sql代码：

    SELECT ename,job,sal FROM emp, (SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno) dept WHERE emp.deptno=dept.deptno AND sal > dept.avgsal;

    3、在DML语句中使用子查询

    子查询不仅适于SELECT语句，也适用于任何DML语句。

    (1)在insert语句中使用子查询

    通过在insert语句中引用子查询，可以将一张表的数据装载到另一张表中去。

    例：将EMP表的数据装载到EMPLOYEE表中。

    sql代码

    INSERT INTO employee(id,name,title,salary) SELECT empno,ename,job,sal FROM emp;

    (2)在update语句中使用子查询

    当在update语句中使用子查询时，即可以在where子句中引用子查询，也可以在SET子句中使用子查询(修改列数据)。

    例：将SMITH同岗位的雇员工资和补助更新为与SMITH的工资和补助完全相同。

    sql代码：

    UPDATE emp SET (sal,comm)=(SELECT sal,comm FROM emp WHERE ename='SMITH') WHERE job= (SELECT job FROM emp WHERE ename='SMITH');

'   (3)在delete语句中使用子查询

    在delete语句中使用子查询时，可以在where子句中引用子查询返回未知值。 

   例：删除SALES部门的所有雇员。

   sql代码

    DELETE FROM emp WHERE deptno= (SELECT deptno FROM dept WHERE dname='SALES');

    4、在DDL语句中使用子查询

    除了可以在select、insert、update、delete语句中使用子查询外，也可以在DDL语句中使用子查询。注意，当在select和dml语句中使用子查询时，where子句和set子句的子查询语句不能包含order by子句；但在DDL语句中使用子查询时，子查询可以包含Oorder by 子句。

    (1)在create table 语句中使用子查询

     通过在create table中使用子查询，可以在建立新表的同时复制表的数据。

     例：建立new_emp表，并将emp表的数据复制到该表。

     CREATE TABLE new_emp(id,name,sal,job,deptno) AS SELEct empno,ename,sal,job,deptno FROM emp;

     (2)在create vies语句中使用子查询

     建立视图时，必须指定视图所对应的子查询语句。

     CREATE OR REPLACE  VIEW dept_10 AS SELECT empno,ename,job,sal,deptno FROM emp WHERE deptno=10 ORDER BY empno;