大表过滤场景：SELF-JOIN WHERE与CASE WHEN的性能差异对比

针对你提出的大表场景下两种实现的性能问题，结论很明确：CASE WHEN的写法性能远优于自连接版本，原因如下：

• 执行逻辑的本质差异
  CASE WHEN是单表单次扫描：遍历表中每一行时，仅做一次简单的条件判断（职业是否为doctor），直接生成目标列，全程只需要访问一次表数据。
  而自连接版本需要两次表访问：先通过子查询扫描表过滤出医生数据，再将子查询结果和原表做左连接。连接操作本身需要额外的逻辑处理（比如哈希匹配、嵌套循环），大表场景下这种额外开销会被显著放大。

• 资源占用对比
  自连接会消耗更多CPU、内存资源：连接操作需要构建临时数据结构（比如哈希表）来匹配关联键，大表情况下临时数据的存储和计算都会占用大量资源。而CASE WHEN只是行级的轻量计算，几乎没有额外资源消耗。

• 潜在的数据风险
  你的自连接写法还存在数据重复的隐患：如果name不是唯一键（比如存在同名不同人的情况），左连接后会生成重复行，完全不符合你“新增一列标记医生”的需求，而CASE WHEN不会出现这个问题。

• 执行计划的直观验证
  查看数据库执行计划就能明显看到差异：CASE WHEN的计划只有一次表扫描（或索引扫描，如果profession有索引的话）；自连接的计划会包含子查询扫描+连接操作，成本值远高于前者。

综上，你的需求用CASE WHEN完全足够，既简洁易读，性能又最优，自连接的写法完全没有必要使用。

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