51.Oracle数据库SQL开发之 子查询——编写关联子查询

51.Oracle数据库SQL开发之 子查询——编写关联子查询

欢迎转载，转载请标明出处：http://blog.csdn.net/notbaron/article/details/49805713

关联子查询会引用外部SQL语句中的一列或多列。这种子查询之所以被称为关联子查询，是因为它们通过相同的列与外部的SQL语句关联。

1.  关联子查询的例子

关联子查询检索那些价格高于同类产品平均价格的产品：

store@PDB1> selectproduct_id,product_type_id,name,price from products outer where price >(select avg(price) from products inner whereinner.product_type_id=outer.product_type_id);

 

PRODUCT_ID PRODUCT_TYPE_ID NAME                               PRICE

---------- --------------------------------------------- ----------

          2              1 Chemistry                                 30

          5              2 Z Files                           49.99

          7              3 Space Force 9                      13.49

         10              4 Pop 3                           15.99

         11              4 Creative Yell                         14.99

其中其中outer用来标记外部查询，inner用来标记内部子查询。内部子查询和外部查询通过product_type_id列关联起来。

         关联子查询子查询中，外部查询中的每一行都被一次一行的传递给子查询。子查询依次读取外部查询中的每一行的值，并将其应用到子查询上，直到外部查询中的所有行都被处理完为止，然后返回整个查询的结果。

2.  在关联子查询中使用EXIST和NOT EXIST

EXISTS操作符用于检查子查询返回行的存在性。

2.1         在关联子查询中使用EXISTS

执行如下：

store@PDB1> select employee_id,last_namefrom employees outer where exists (select employee_id from employees innerwhere inner.manager_id=outer.employee_id);

 

EMPLOYEE_ID LAST_NAME

----------- ----------

           1 Smith

           2 Johnson

2.2         在关联子查询中使用NOT EXISTS

执行如下：

store@PDB1> select product_id,name from productsouter where not exists (select 1 from purchases inner whereinner.product_id=outer.product_id);

 

PRODUCT_ID NAME

---------- ------------------------------

          4 Tank War

          5 Z Files

          6 2412: The Return

          7 Space Force 9

          8 From Another Planet

          9 Classical Music

         10Pop 3

         11Creative Yell

         12My Front Line

 

9 rows selected.

2.3         EXISTS和NOT EXISTS与IN和NOT IN 的比较

EXISTS与IN不同：EXISTS只是检查行的存在性，而IN则要检查实际值的存在性。通常EXISTS性能都比IN高。

         当包含一个空值时，NOTEXISTS返回true，而NOT IN则返回false.

store@PDB1> select product_type_id,name fromproduct_types outer where not exists( select 1 from products inner whereinner.product_type_id=outer.product_type_id);

 

PRODUCT_TYPE_ID NAME

--------------- ----------

               5 Magazine

然后执行如下：

store@PDB1> select product_type_id,name fromproduct_types where product_type_id not in ( select product_type_id fromproducts);

 

no rows selected

         因为子查询返回中包含了一个空值。所以NOT IN 返回为FALSE。

可以通过如下解决：

store@PDB1> select product_type_id,name fromproduct_types where product_type_id not in ( select NVL(product_type_id,0) fromproducts);

 

PRODUCT_TYPE_ID NAME

--------------- ----------

               5 Magazine