为何带冗余谓词的LEFT JOIN性能优于CROSS JOIN？

• 首先是两个查询的执行逻辑差异：
  CROSS JOIN的语义是强制将左表（筛选后ID>2000的Employee表）每一行，和右表（CD日期子查询）的全部行生成笛卡尔积。执行计划会优先全量扫描Numbers表取出所有符合N <= DATEDIFF(day, @PeriodStart, @PeriodEnd)的行，再和Employee的结果做关联，所以总读取行数是右表总行数 + 左表行数。
• 带CD.ClockDate = CD.ClockDate的LEFT JOIN触发了优化器的特殊优化：
  1. LEFT JOIN本身的语义是保留左表所有行，右表无匹配时补NULL，而CD.ClockDate = CD.ClockDate是右表字段的恒真谓词，优化器判定右表任意行都可以和左表行匹配，因此选择了左表驱动的嵌套循环关联：不需要预先拉取全量右表数据，而是每取一行Employee数据，才去执行一次右表的子查询。
  2. 因为你的测试场景中@PeriodStart = @PeriodEnd，右表子查询每次执行只会返回1行结果，所以Numbers表的总读取行数刚好等于Employee筛选后的行数（也就是你观察到的3000行左右），远低于CROSS JOIN的读取量。就算周期跨度是多天，右表每次返回的行数是固定的天数，总读取量依然是「左表行数 * 天数」，比CROSS JOIN的「右表总行数 + 左表行数 * 右表总行数」小很多。
• 为什么替换关联条件后性能回退：
  当你把关联条件换成1=1或者CD.N = CD.N时，优化器无法将关联条件和右表的DATEADD计算做绑定，不会触发下推优化，会回退到和CROSS JOIN一致的执行逻辑：先全量拉取右表所有数据，再做笛卡尔积，因此性能和CROSS JOIN持平。
• DATEADD的计算时机：
  SQL Server确实会在关联匹配前计算DATEADD的结果，而且因为关联条件是DATEADD结果的恒等判断，优化器可以提前做常量折叠，判定右表子查询的返回行数，从而选择更优的下推执行计划。

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