欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

关联子查询

程序员文章站 2022-06-10 08:58:47
...

    何为关联子查询?这里引用《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语句被优化了,也可能是关联子查询缓存了每次子查询的结果。