6.5 子查询
子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从Oracle 4.1开始引入。在SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。子查询中常用的操作符有ANY（SOME）、ALL、IN、EXISTS。子查询可以添加到SELECT、UPDATE和DELETE语句中，而且可以进行多层嵌套。子查询中也可以使用比较运算符，如“＜”“＜=”“＞”“＞=”和“!=”等。本节将介绍如何在SELECT语句中嵌套子查询。
6.5.1 带ANY、SOME关键字的子查询
ANY和SOME关键字是同义词，表示满足其中任一条件，它们允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件。
下面定义两个表tbl1和tbl2：
分别向两个表中插入数据：
ANY关键字接在一个比较操作符的后面，表示若与子查询返回的任何值比较为TRUE，则返回TRUE。
【例6.52】返回tbl2表的所有num2列，然后将tbl1中的num1的值与之进行比较，只要大于num2的任何一个值，即为符合查询条件的结果。
在子查询中，返回的是tbl2表的所有num2列结果（6,14,11,20），然后将tbl1中的num1列的值与之进行比较，只要大于num2列的任意一个数即为符合条件的结果。
6.5.2 带ALL关键字的子查询
ALL关键字与ANY和SOME不同，使用ALL时需要同时满足所有内层查询的条件。例如，修改前面的例子，用ALL关键字替换ANY。
ALL关键字接在一个比较操作符的后面，表示与子查询返回的所有值比较为TRUE，则返回TRUE。
【例6.53】返回tbl1表中比tbl2表num2列所有值都大的值，SQL语句如下：
在子查询中，返回的是tbl2的所有num2列结果（6,14,11,20），然后将tbl1中的num1列的值与之进行比较，大于所有num2列值的num1值只有27，因此返回结果为27。
6.5.3 带EXISTS关键字的子查询
EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询。如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层语句将不进行查询。
【例6.54】查询suppliers表中是否存在s_id=107的供应商，如果存在，则查询fruits表中的记录，SQL语句如下：
由结果可以看到，内层查询结果表明suppliers表中存在s_id=107的记录，因此EXISTS表达式返回true。外层查询语句接收true之后对表fruits进行查询，返回所有的记录。
EXISTS关键字可以和条件表达式一起使用。
【例6.55】查询suppliers表中是否存在s_id=107的供应商，如果存在，则查询fruits表中f_price大于10.20的记录，SQL语句如下：
由结果可以看到，内层查询结果表明suppliers表中存在s_id=107的记录，因此EXISTS表达式返回true。外层查询语句接收true之后根据查询条件f_price>10.20对fruits表进行查询，返回结果为4条f_price大于10.20的记录。
NOT EXISTS与EXISTS使用方法相同，返回的结果相反。子查询如果至少返回一行，那么NOT EXISTS的结果为false，此时外层查询语句将不进行查询。如果子查询没有返回任何行，那么NOT EXISTS返回的结果是true，此时外层语句将进行查询。
【例6.56】查询suppliers表中是否存在s_id=107的供应商，如果不存在则查询fruits表中的记录，SQL语句如下：
查询语句SELECT s_name FROM suppliers WHERE s_id=107，对suppliers表进行查询返回了一条记录，NOT EXISTS表达式返回false，外层表达式接收false，将不再查询fruits表中的记录。
提示
EXISTS和NOT EXISTS的结果只取决于是否会返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。
6.5.4 带IN关键字的子查询
IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作。
【例6.57】在orderitems表中查询f_id为c0的订单号，并根据订单号查询具有订单号的客户c_id，SQL语句如下：
查询结果的c_id有两个值，分别为10001和10004。上述查询过程可以分步执行，首先内层子查询查出orderitems表中符合条件的订单号，单独执行内查询，查询结果如下：
可以看到，符合条件的o_num列的值有两个：30003和30005，然后执行外层查询，在orders表中查询订单号等于30003或30005的客户c_id。嵌套子查询语句还可以写为如下形式，实现相同的效果：
这个例子说明在处理SELECT语句的时候，Oracle实际上执行了两个操作过程，即先执行内层子查询，再执行外层查询，内层子查询的结果作为外部查询的比较条件。
SELECT语句中可以使用NOT IN关键字，其作用与IN正好相反。
【例6.58】与前一个例子类似，但是在SELECT语句中使用NOT IN关键字，SQL语句如下：
这里返回的结果有3条记录，由前面可以看到，子查询返回的订单值有两个，即30003和30005，但为什么这里还有值为10001的c_id呢？这是因为c_id等于10001的客户的订单不止一个，可以查看订单表orders中的记录。
可以看到，虽然排除了订单号为30003和30005的客户c_id，但是o_num为30001的订单与30005都是10001号客户的订单。所以结果中只是排除了订单号，但是仍然有可能选择同一个客户。
提示
子查询的功能也可以通过连接查询完成，但是子查询使得Oracle代码更容易阅读和编写。
6.5.5 带比较运算符的子查询
在前面介绍的带ANY、ALL关键字的子查询时使用了“＞”比较运算符，子查询时还可以使用其他的比较运算符，如“＜”“＜=”“=”“＞=”和“!=”等。
【例6.59】在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类，SQL语句如下：
该嵌套查询首先在suppliers表中查找s_city等于Tianjin的供应商的s_id，单独执行子查询查看s_id的值，执行下面的操作过程：
然后在外层查询时，在fruits表中查找s_id等于101的供应商提供的水果的种类，查询结果如下：
结果表明，“Tianjin”地区的供应商提供的水果种类有3种，分别为“apple”“blackberry”“cherry”。
【例6.60】在suppliers表中查询s_city等于“Tianjin”的供应商s_id，然后在fruits表中查询所有非该供应商提供的水果的种类，SQL语句如下：
该嵌套查询执行过程与前面相同，在这里使用了不等于“＜＞”运算符，因此返回的结果和前面正好相反。