1. 單行子查詢
select name from emp where name = (select name from dept where address = 'XXX');
2. 多行子查詢
//查詢包含該內(nèi)容
select name from emp where name in (select name from dept where dname like '%XXX%');
//查詢不包含給內(nèi)容
select name from emp where name not in (select name from dept where dname like '%XXX%');
3. 多列子查詢
select name from emp where (name,sal) in (select name,max(sal) from emp group by time);
4.內(nèi)聯(lián)視圖子查詢
(1)SELECT ename,job,sal,rownum FROM (SELECT ename,job,sal FROM EMP ORDER BY sal);
(2)SELECT ename,rownum FROM ( SELECT ename FROM EMP ORDER BY sal) WHERE rownum <= 5;
5.在having子句中使用子查詢
SELECT TeacherID, AVG(Age) AS AGE FROM Student GROUP BY TeacherID HAVING AVG(Age) > 12
HAVING:給分組設(shè)置條件
6. not exists和exists的用法，優(yōu)化使用這個(gè)方案，不要使用in和not in 查詢結(jié)果集慢，無(wú)法使用索引
select * from A where exists (select * from B where A.id = B.id) //結(jié)果為真則返回結(jié)果集
select * from A where not exists (select * from B where A.id = B.id) //結(jié)果不為真則結(jié)果集
