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表中的记录。

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号客户的订单。所以结果中只是排除了订单号，但是仍然有可能选择同一个客户。

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语句如下：

该嵌套查询执行过程与前面相同，在这里使用了不等于“＜＞”运算符，因此返回的结果和前面正好相反。