1. 单行子查询
select name from emp where name = (select name from dept where address = 'XXX');
2. 多行子查询
//查询包含该内容
select name from emp where name in (select name from dept where dname like '%XXX%');
//查询不包含给内容
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.内联视图子查询
(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:给分组设置条件
6. not exists和exists的用法，优化使用这个方案，不要使用in和not in 查询结果集慢，无法使用索引
select * from A where exists (select * from B where A.id = B.id) //结果为真则返回结果集
select * from A where not exists (select * from B where A.id = B.id) //结果不为真则结果集
