关联子查询

    何为关联子查询？这里引用《Oracle Database 10g SQL开发指南》中的解释：

  关联子查询会引用外部查询中的一列或多列。这种子查询之所以被称为关联子查询，是因为子查询的确与外部查询有关。当问题的答案需要依赖于外部查询中包含的每一行中的值时，通常就需要使用关联子查询。

 

    products表中有如下数据：

SQL> SELECT * FROM products;
 
PRODUCT_ID PRODUCT_TYPE_ID NAME                   DESCRIPTION            PRICE
---------- --------------- ---------------------- -------------------- -------
         1               1 Modern Science         A description of mod   19.95
                                                  ern science          
 
         2               1 Chemistry              Introduction to Chem   30.00
                                                  istry                
 
         3               2 Supernova              A star explodes        25.99
         4               2 Tank War               Action movie about a   13.95
                                                   future war          
 
         5               2 Z Files                Series on mysterious   49.99
                                                   activities          
 
         6               2 2412: The Return       Aliens return          14.95
         7               3 Space Force 9          Adventures of heroes   13.49
         8               3 From Another Planet    Alien from another p   12.99
                                                  lanet lands on Earth 
 
         9               4 Classical Music        The best classical m   10.99
                                                  usic                 
 
        10               4 Pop 3                  The best popular mus   15.99
                                                  ic                   
 
        11               4 Creative Yell          Debut album            14.99
        12                 My Front Line          Their greatest hits    13.49
 
12 rows selected

    现在，我们想查询检索那些价格高于同类产品的平均价格的产品。

 

1. 联表查询

    最初，我写查询时想到的是联表查询，SQL如下：

SELECT product_id, name, price
  FROM products p,
       (SELECT product_type_id, AVG(price) avg_price
          FROM products
         GROUP BY product_type_id) pa
 WHERE p.product_type_id = pa.product_type_id
   AND p.price > pa.avg_price;

    将同类产品的平均价格作为内联视图，然后联表查询。这种方式很容易想到，写起来也不难。

 

2. 关联子查询

    SQL如下：

SELECT product_id, name, price
  FROM products
 outer WHERE price >
             (SELECT AVG(price)
                FROM products
               inner WHERE outer.product_type_id = inner.product_type_id
             );

现在对这个SQL做个解释，来自上面提到的书：

  这个查询中使用了两个别名：outer用来标记外部查询，inner用来标记内部查询。内部查询和外部查询使用product_type_id列关联起来。
  在关联子查询中，外部查询中的每一行都被一次一行地传递给子查询。子查询依次读取外部查询中的每一行的值，并将其应用到子查询上，直到外部查询中的所有的行都被处理完为止。然后返回整个查询结果。
  在前面这个例子中，外部查询从products表中检索出所有的行，并将其传递给内部查询。内部查询依次读取外部查询传递来的每一行数据，并对内部查询中product_type_id等于外部查询中product_type_id值得每种产品计算平均价格。

 

一点思考

    按照上面的解释，使用关联子查询时，每种产品的平均价格会被多次计算，因为products表中有重复的product_type_id列，而使用联表查询只会被计算1次。那么哪种性能高呢？

    在PLSQL上测试发现，两种方法时间相同，都是0.016 seconds。

    也许，SQL语句被优化了，也可能是关联子查询缓存了每次子查询的结果。