一文详解SQL关联子查询

简介： 本文主要介绍什么是关联子查询以及如何将关联子查询改写为普通语义的sql查询。

本文主要介绍什么是关联子查询以及如何将关联子查询改写为普通语义的sql查询。

在背景介绍中我们将讲讲常见的关联子查询的语义，关联子查询语法的好处以及其执行时对数据库系统的挑战。第二章中我们将主要介绍如何将关联子查询改写为普通的查询的形式，也就是解关联。第三章中我们会介绍解关联中的优化方法。

一 背景介绍

关联子查询是指和外部查询有关联的子查询，具体来说就是在这个子查询里使用了外部查询包含的列。

因为这种可以使用关联列的灵活性，将sql查询写成子查询的形式往往可以极大的简化sql以及使得sql查询的语义更加方便理解。下面我们通过使用tpch schema来举几个例子以说明这一点。tpch schema是一个典型的订单系统的database，包含customer表，orders表，lineitem表等，如下图：

假如我们希望查询出“所有从来没有下过单的客户的信息”，那么我们可以将关联子查询作为过滤条件。使用关联子查询写出的sql如下。可以看到这里的not exists子查询使用列外部的列c_custkey。

-- 所有从来没有下过单的客户的信息
select c_custkey
from
  customer
where
  not exists (
    select
      *
    from
      orders
    where
      o_custkey = c_custkey
  )

如果不写成上面的形式，我们则需要考虑将customer和orders两个表先进行left join，然后再过滤掉没有join上的行，同时我们还需要markorder的每一行，使得本来就是null的那些。查询sql如下：

-- 所有从来没有下过单的客户的信息
select c_custkey
from
  customer
  left join (
    select
      distinct o_custkey
    from
      orders
  ) on o_custkey = c_custkey
where
  o_custkey is null

从这个简单的例子中就可以看到使用关联子查询降低了sql编写的难度，同时提高了可读性。

除了在exists/in子查询中使用关联列，关联子查询还可以出现在where中作为过滤条件需要的值。比如tpch q17中使用子查询求出一个聚合值作为过滤条件。

-- tpch q17
SELECT Sum(l1.extendedprice) / 7.0 AS avg_yearly 
FROM   lineitem l1, 
       part p
WHERE  p.partkey = l1.partkey 
       AND p.brand = 'Brand#44' 
       AND p.container = 'WRAP PKG' 
       AND l1.quantity < (SELECT 0.2 * Avg(l2.quantity) 
                         FROM   lineitem l2
                         WHERE  l2.partkey = p.partkey);

除了出现在where里面，关联子查询可以出现在任何允许出现单行(scalar)的地方，比如select列表里。如果我们需要做报表汇总一些customer的信息，希望对每一个customer查询他们的订单总额，我们可以使用下面包含关联子查询的sql。

-- 客户以及对应的消费总额
select
  c_custkey,
  (
    select sum(o_totalprice)
    from
      orders
    where o_custkey = c_custkey 
    ）
from
  customer

更复杂一些的比如，我们希望查询每一个customer及其对应的在某个日期前已经签收的订单总额。利用关联子查询只需要做一些小的改变如下：

select
  c_custkey,
  (
    select
      sum(o_totalprice)
    from
      orders
    where
      o_custkey = c_custkey
      and '2020-05-27' > (
        select
          max(l_receiptdate)
        from
          lineitem
        where
          l_orderkey = o_orderkey
      ) 
    ）
from
   customer

看了这些例子，相信大家都已经感受到使用关联子查询带来的便捷。但是同时关联子查询也带来了执行上的挑战。为了计算关联结果的值（子查询的输出），需要iterative的执行方式。

以之前讨论过的tpch 17为例子：

SELECT Sum(l1.extendedprice) / 7.0 AS avg_yearly 
FROM   lineitem l1, 
       part p
WHERE  p.partkey = l1.partkey 
       AND p.brand = 'Brand#44' 
       AND p.container = 'WRAP PKG' 
       AND l1.quantity < (SELECT 0.2 * Avg(l2.quantity) 
                         FROM   lineitem l2
                         WHERE  l2.partkey = p.partkey);

这里的子查询部分使用了外部查询的列 p.partkey。

SELECT 0.2 * Avg(l2.quantity) 
FROM   lineitem l2
WHERE  l2.partkey = p.partkey  -- p.partkey是外部查询的列

优化器将这个查询表示为如下图的逻辑树：

如果数据库系统不支持查看逻辑树，可以通过explain命令查看物理计划，一般输出如下图：

+---------------+
| Plan Details  |
+---------------+
 1- Output[avg_yearly] avg_yearly := expr
 2    -> Project[] expr := (`sum` / DOUBLE '7.0')
 3        - Aggregate sum := `sum`(`extendedprice`)
 4            -> Filter[p.`partkey` = l1.`partkey` AND `brand` = 'Brand#51' AND `container` = 'WRAP PACK' AND `quantity` < `result`]
 5