SQL子查询总结

在一个SQL语句中嵌套另一个SQL语句成为子查询。
当一个问题无法通过一部解决时可考虑使用子查询，一个出现在SELECT语句的FROM子句中的子查询被称为"内嵌视图"；一个出现在SELECT语句的WHERE子句中的子查询被称为"内嵌子查询"，一个子查询能够包含另一个子查询，在一个顶级的查询中，Oracle数据库没有限制在FROM子句中的嵌套层数，可以在一个WHERE子句中嵌套255层子查询
子查询其实是指嵌入在其他SQL语句中的SELECT语句，也称为嵌套查询。子查询就是位于SELECT、UPDATE、或DELETE语句中内部的查询。
注意，当在DDL语句中引用子查询时，可以带有ORDER BY子句；
但是当在WHERE子句，SET子句中引用子查询时，不能带有ORDER BY子句。


子查询注意的问题：
1.要将子查询发在圆括号内
2.子查询可出现在WHERE子句、FROM子句、SELECT列表(此处只能是一个单行子查询)、HAVING子句中
3.子查询不能出现在主查询的GROUP BY语句中
4.子查询和主查询使用表可以不同，是要子查询返回的结果能够被主查询使用即可
5.一般不会在子查询中使用ORDER BY语句，但在TOP-N分析中必须使用ORDER BY语句
6.当行子查询只能使用单行操作符，多行子查询只能使用多行操作符
7.采用合理的缩进和换行来提过SQL语句的可读性
8.子查询中的空值问题
 


1.单行子查询：只返回一行数据的子查询语句
SELECT ename,sal,deptno FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='Tom');
 
2.多行子查询：是指返回多行数据的子查询语句，在WHERE使用多行子查询时，要用IN、ALL、ANY
IN：匹配于子查询结果的任一个值即可。
ALL：必须要符合子查询查询结果的所有值。
ANY：只要符合子查询结果的人一个值即可。
 
IN操作符
SELECT ename,job,sal,deptno FROM emp WHERE job IN
(SELECT diatinct job FROM emp WHERE deptno=10);
 
ALL操作符
SELECT ename,sal,deptno FROM emp WHERE sal>ALL
(SELECT sal FROM emp WHERE deptno=30);
 
ANY操作符
SELECT ename,sal,deptno FROM emp WHERE sal>ANY
(SELECT sal FROM emp WHERE deptno=30);
 
3.多列子查询
SELECT ename,job,sal,deptno FROM emp WHERE (deptno,job)=
(SELECT deptno,job FROM emp WHERE ename='SMITH');
 
☆成对比较
SELECT ename,sal,comm,deptno FROM emp
WHERE (sal,nvl(comm,-1)) IN (SELECT sal,nvl(comm,-1)
FROM emp WHERE deptno=30);
 
☆非成对比较
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);
 
 
4.其他子查询
☆相关子查询：是指需要引用主查询表列的 子查询语句。相关子查询是通过EXISTS谓词来实现的。
SELECT ename,job,sal,deptno FROM emp WHERE EXISTS
(SELECT 1 FROM dept WHERE dept.deptno=emp.deptno AND dept.loc='NEW YORK');
 
 
☆在FROM子句中使用子查询
当在FROM子句中使用子查询，该子查询会被作为视图对待，要给子查询指定别名。
SELECT ename,job,sal FROM emp,
(SELECT deptno,avg(sal) as avgsal FROM emp
GROUP BY deptno) dept
WHERE emp.deptno= dept.deptno AND sal>dept.avgsal;
 
 
☆在DML语句中使用子查询
1.INSERT
INSERT INTO employee (id,name,title,salary)
SELECT emptno,ename,job,sal FROM emp;
 
2..UPDATE
UPDATE emp SET (sal,comm)=
(SELECT sal,comm FROM emp WHERE ename='SMITH')
WHERE job=(SELECT job FROM emp WHERE ename='SMITH');
 
3.DELETE
DELECT FROM emp WHERE deptno=
(SELECT deptno FROM dept WHERE dname='SALES');
 
☆在DDL语句中使用子查询
1.CREATE TABLE
CREATE TABLE new_emp(id,name,sal,job,deptno)AS
SELECT emptno,ename,sal,joob,deptno FROM emp;
 
2.CREATE VIEW
CREATE OR REPLACE VIEW dept_10 AS
SELECT emptno,ename,job,sal,deptno FROM emp
WHERE deptno=10 ORDER BY emptno;
 
3.CREATE MATERIALIZED VIEW
建立实体化视图
 
CREATE MATERIALIZED VIEW summary_emp AS
SELECT deptno,job,avg(sal) avgsal,sum(sal) sumsal
FROM emp GROUP BY cube(deptno,job);