JoinOnConstant(常数)的逻辑拾遗

Published on with 0 views and 0 comments

热身

在开始之前，请先看下以下四条SQL，请问它们的数据逻辑是一样的吗，或者说它们的结果必然一样的吗？（这里不考虑SQL的效率，优化）

-- SQL1
select a.k1, a.v1, b.k2, b.v2
from A a 
left join B b on a.k1=b.k2 and b.status = 0
where a.status = 0

-- SQL2
select a.k1, a.v1, b.k2, b.v2
from A a left join B b on a.k1 = b.k2
where a.status = 0 and b.status = 0

-- SQL3
select a.k1, a.v1, b.k2, b.v2
from A a 
left join
(
    select k2, v2 from B where status = 0
) b on a.k1 = b.k2
where a.status = 0

-- SQL4
select a.k1, a.v1, b.k2, b.v2
from 
(
    select k1, v1 from A where status = 0
) a 
left join
(
    select k2, v2 from B where status = 0
) b on a.k1 = b.k2

现在公布答案

假设A,B表分别为以下数据

A B
k1v1status k2v2status
1a10 1b10
2a20 2b21
3a30 2b220
4a41 4b40

那么最后执行的结果如下，可以发现SQL1， SQL3，SQL4的逻辑是一样的，而SQL2的逻辑则不同，但实际情况下大家更有可能写成SQL2的形式

SQL1 SQL2 SQL3 SQL4
k1v1k2v2 k1v1k2v2 k1v1k2v2 k1v1k2v2
1a11b1 1a11b1 1a11b1 1a11b1
2a22b22 2a22b22 2a22b22 2a22b22
3a3 3a3 3a3

为什么

实际上仔细分析SQL的执行流程，不难就容易分析出来。简要概括起来，一条LEFT JOIN SQL的大致执行逻辑如下（此处不考虑group by, order by等语法，以及分布式表，驱动表where语句下推等情况）

  1. 先分别从a表，b表取出用到的字段的所有记录
  2. 然后根据ON语句中的逻辑，把数据关联成一个临时表T
  3. 然后在根据WHERE语句的过滤条件从临时表T中过滤出对应记录
  4. 按SELECT语句中的字段列表输出结果

此时我们先回头看下SQL1，他的临时表T1数据如下

k1v1a.statusk2v2b.status
1a101b10
2a202b220
3a30
4a414a40

因为在第2步只让b表中status = 0的记录参与关联，估临时表中b表的status都是0，而a的所有记录都保留，此时在T1上做3. WHERE a.status = 0的过滤，以及4. SELECT a.k1, a.v1, b.k2, b.v2的数据，就不难得出上述的最终答案了。

此时我们再回头来看SQL2，他的临时T2数据为

k1v1a.statusk2v2b.status
1a101b10
2a202b21
2a202b220
3a30
4a414a40

此时在T2上做3. WHERE a.status = 0 and b.status = 0的过滤，以及4. SELECT a.k1, a.v1, b.k2, b.v2的数据，同样不难得出上述的最终答案了。

SQL3， SQL4留给读者自行测试推导

真实场景

这里就简要举些栗子

  1. 用数据表与维度表做关联，有以下几点要求 a. 数据表中的数据必须全部保留 b. 维度表的记录为软删除，即用一个字段来标记是否已经删除 c. 不能关联出已经删除的维度记录。
    • 错误的SQL2将会造成要求a无法满足
  2. 用维度表去关联其扩展信息表，比如用媒体表去关联其供应商信息，同样有以下几点要求 a.维度左表的所有有效记录都保留 b. 扩展表的记录为乱删除 c.不能关联出已经删除的维度记录
    • 错误的SQL2将会造成维度数据丢失，及无法满足要求a
标题：JoinOnConstant(常数)的逻辑拾遗
作者：wuqingbo
地址：http://solo.fancydigital.com.cn/articles/2021/12/10/1639129994707.html

↑ ↓