勿传网上！严禁谋利！ Oracle学习笔记

常彦博

十一、非关联子查询

子查询就是在一条SQL（DDL、DML、TCL、DQL、DCL）语句中嵌入select语句。

11.1语法

select colname,? from tabname where expr operator(select colname2 from subtabname);

11.2子查询的执行过程

先执行子查询，子查询的返回结果作为主查询的条件，再执行主查询。子查询只执行一遍。若子查询的返回结果为多个值，Oracle会自动去掉重复值后，再将结果返回给主查询。 注意事项：不需要distinct，会自动去重的。 eg1：哪些os帐号的开通时间是最早的 select unix_host,os_username,create_date from service where create_date = (select min(create_date) from service); eg2：哪些os帐号的开通时间比unix服务器192.168.0.26上的huangr晚 select unix_host,create_date,os_username from service where create_date > ( select create_date from service where os_username = 'huangr' and unix_host = '192.168.0.26'); eg3：哪些os帐号的开通时间比huangr晚？（多台unix服务器上都有名为huangr的os帐号） select unix_host,create_date,os_username from service where create_date > all (select create_date from service where os_username = 'huangr');大于所有的 where create_date > (select max(create_date) from service where os_username = 'huangr');大于最大的 where create_date > any (select create_datefrom service where os_username = 'huangr');大于任意一个 where create_date > (select min(create_date) from service where os_username = 'huangr');大于最小的 11.3常见错误 单行子查询返回多条记录！此时要注意运算符的选择： 1）若子查询的返回结果仅为一个值，可用单值运算符，如“=”号。 2）若子查询的返回结果可能为多个值，必须用多值运算符，如in等。 eg：哪些客户是推荐人 select real_name from account where id in (select recommender_id from account); 11.4子查询与空值

若子查询的返回结果中包含空值null，并且运算为not in，那么整个查询不会返回任何行。not in等价于<>all，任何值跟null比（包括null本身），结果都不为true。

eg：哪些客户不是推荐人 select real_name from account

19

勿传网上！严禁谋利！ Oracle学习笔记

常彦博

where id not in (select recommender_id from account where recommender_id is not null); 11.5多列子查询

where子句后面可以跟多列条件表达式。

eg1：哪些os帐号的开通时间是所在unix服务器上最早的？（每台unix服务器上最早开通的os帐号） select unix_host,os_username,create_date from service where (unix_host,create_date) in (select unix_host,min(create_date) from service group by unix_host); eg2：哪些os帐号的开通时间比所在unix服务器上最早开通时间晚九天 select unix_host,os_username,create_date from service where (unix_host,to_char(create_date,'yyyymmdd')) in (select unix_host,to_char(min(create_date) + 9,'yyyymmdd') from service group by unix_host); 20

勿传网上！严禁谋利！ Oracle学习笔记

常彦博

十二、关联子查询

关联子查询采用的是循环（loop）的方式。

12.1语法

select column1,?from table1 o where column1 operator

(select column1,column2 from table2 i where i.expr1=o.expr2);

12.2执行过程

1）外部查询得到一条记录（查询先从outer表中读取数据），并将其传入到内部的表查询。 2）内部查询基于传入的值执行。 3）内部查询从其结果中把值传回到外部查询，外部查询使用这些值来完成处理，若符合条件，outer表中得到的那条记录就放入结果集中，否则放弃。 4）重复执行1－3，直到把outer表中的所有记录判断一遍。子查询执行n遍。 eg:哪些os帐号的开通天数比同一台unix服务器上的平均开通天数长。 select unix_host,os_username,create_date,round(sysdate - create_date)open_age from service o where round(sysdate - create_date) > (selectavg(round(sysdate - create_date)) from service i where o.unix_host = i.unix_host); 12.3 exists exists采用的是循环（loop）的方式，判断outer表中是否存在在inner表中找到的一条匹配的记录。 12.4 exists执行过程 1）外部查询得到一条记录（查询先从outer表中读取数据），并将其传入到内部的表进行查询。 2）对inner表中的的记录依次扫描，若根据条件，存在一条记录与outer表中的记录匹配，则立即停止扫描，返回true，将outer表中的记录放入结果集中；若扫描了全部记录，没有任何一条记录符合匹配条件，则返回false，outer表中的该记录被过滤掉，不能出现在结果集中。 3）重复执行1－2，直到把outer表中的所有记录判断一遍。 eg1：哪些客户是推荐人 select real_name from account o where exists (select 1 from account i where o.id = i.recommender_id); //1可随便写，不关心结果什么样，只关心是否有满足的条件返回 eg2：哪些客户申请了远程登录业务 非关联子查询： select real_name from account where id in (select account_id from service); 关联子查询： select real_name from account o where exists (select 1 from service i where o.id = i.account_id); 21

勿传网上！严禁谋利！ Oracle学习笔记

常彦博

12.5 not exists

采用的是循环（loop）的方式，判断outer表中是否不存在记录（它能在inner表中找到匹配的记录）。

12.6 not exists执行过程

1）外部查询得到一条记录（查询先从outer表中读取数据），并将其传入到内部的表进行查询。

2）对inner表中的的记录依次扫描，若根据条件，存在一条记录与outer表中的记录匹配，则立即停止扫描，返回false，将outer表中的记录过滤掉，不能出现在结果集中；若扫描了全部记录，没有任何一条记录符合匹配条件，则返回true，outer表中的该记录放入结果集中。 3）重复执行1－2，直到把outer表中的所有记录判断一遍。 eg1：哪些客户不是推荐人 select real_name from account o where not exists (select 1 from account i where o.id = i.recommender_id); eg2：哪些客户没有申请远程登录业务 非关联子查询： select real_name from account where id not in (select account_id from service); 关联子查询： select real_name from account o where not exists (select 1 from service i where o.id = i.account_id); 12.7 in和exists比较 1）exists是用循环（loop）的方式，有outer表的记录数决定循环次数，对于exists影响最大，所以，外表的记录数要少。 2）in先执行子查询，子查询的结果去重之后，再执行主查询，所以，子查询的返回结果越少，越适合用该方式。 22