常规WHERE过滤与布尔值过滤的区别及两种CTE实现优劣对比

现有数据表 #

main.table #

other.table #

需求：关联两张表，在BI工具中创建包含domestic类型order_id的有用链接的新表。

CTE.v1实现 #

CTE.v1 as(select ot.order_id, ot.order_type from other.table as ot where client_id in (1,2))

Select 
(mt.order_id) as id,
(Select 'This is your useful link #1'),
(Select 'This is your useful link #2')
from main.table as mt 
join CTE.v1 AS ot on mt.order_id = ot.order_id
where order_type='domestic'

CTE.v2实现 #

CTE.v2 as(
select ot.order_id, ot.order_value, 
iff(sum(iff(ot.order_type = 'Domestic', 1, 0)) > 0, true, false) is_domestic 
from other.table as ot where client_id in (1,2))
    
Select 
(mt.order_id) as id,
(Select 'This is your useful link #1'),
(Select 'This is your useful link #2')
from main.table as mt 
join CTE.v2 AS ot on mt.order_id = ot.order_id
where ot.is_domestic

核心区别 #

• 过滤逻辑与时机
  • CTE.v1是先筛选指定client_id的订单，关联主表后直接用order_type='domestic'过滤，逻辑直白，只保留明确为国内类型的单条记录。
  • CTE.v2是在CTE阶段通过聚合计算生成布尔列is_domestic，判断某个order_id是否存在至少一条国内订单，关联后靠这个布尔值过滤。
• 重复数据处理
  • 如果other.table中同一个order_id对应多条不同类型的记录（比如同一订单既有国内又有国际记录），CTE.v1会保留该订单下所有domestic类型的行，关联主表后会产生重复的主表数据。
  • CTE.v2会对同一个order_id的记录做聚合合并，只要有一条国内类型就标记is_domestic=true，关联后主表数据不会重复。
• 性能表现
  • CTE.v1逻辑简单，数据库优化器更容易生成高效执行计划，尤其是order_type字段有索引时，过滤速度更快。
  • CTE.v2因为用到聚合分组，数据量较大时计算成本更高，性能比v1弱。

CTE.v2是否冗余？ #

• 不是冗余操作，它的核心优势是处理多类型关联的订单场景：当一个order_id对应多种order_type时，v2既能快速标记该订单是否包含国内类型，又能自动去重，避免关联后数据重复。
• 但如果你的业务场景里每个order_id只会对应一种order_type（就像示例数据这样），那CTE.v2确实属于冗余操作，此时v1更简洁高效。

内容的提问来源于stack exchange，提问作者Pandafreak