SQL子查询

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) //结果不为真则结果集