如何让SQL Server query planner在非唯一键连接时消除left join？

背景：SQL Server的连接消除特性 #

SQL Server查询优化器有个实用功能：如果查询没用到关联表/视图的任何列，而且这个连接不会改变查询返回的行数（基数），就会自动把这个连接从执行计划里删掉。比如你建了一个多表关联的视图，当只查询视图里的部分列时，很多关联的表根本不会被访问。

满足“不影响基数”的情况分两种：

• 和另一表做INNER JOIN，并且存在外键关系
• 基于唯一列集合和另一表做LEFT JOIN，这种情况下去掉连接也不会改变返回的行数

遇到的问题 #

我有一个视图，它LEFT JOIN了几个结构复杂的其他视图。我明确知道这个LEFT JOIN不会改变行数——因为连接列在底层视图里最多返回一行，但查询优化器识别不了这一点，还是会把底层视图里的一大堆表都放进执行计划里。而且现在没法简化底层视图、改成索引视图或者替换成表。

现在需要解决的是：不管LEFT JOIN的是视图还是表，怎么确保当没选这个连接对象的任何列时，查询能自动消除这个LEFT JOIN？

解决方案 #

核心思路是给查询优化器明确的提示，让它知道这个LEFT JOIN不会增加返回行数。以下是几种可行的方法：

方法1：用带DISTINCT的子查询包装关联对象 #

把要LEFT JOIN的视图或表套进一个子查询里，只保留连接列，同时加上DISTINCT关键字，让优化器明确这里的连接列是唯一的：

SELECT main.*
FROM main_table main
LEFT JOIN (
    SELECT DISTINCT join_col -- 仅保留连接列，DISTINCT保证唯一性
    FROM complex_view
) sub ON main.id = sub.join_col

当主查询没引用sub的任何列时，优化器就会自动消除这个LEFT JOIN，不会去访问complex_view里的冗余表。

方法2：用GROUP BY连接列 #

通过GROUP BY连接列，同样能让优化器识别出每个连接值只会返回一行：

SELECT main.*
FROM main_table main
LEFT JOIN (
    SELECT join_col
    FROM complex_view
    GROUP BY join_col -- GROUP BY确保每个join_col仅返回一行
) sub ON main.id = sub.join_col

方法3：TOP (100) PERCENT配合ORDER BY（部分场景适用） #

这个方法在SQL Server 2005及以后版本的优化器中效果可能有限，但可以尝试：

SELECT main.*
FROM main_table main
LEFT JOIN (
    SELECT TOP (100) PERCENT join_col
    FROM complex_view
    ORDER BY join_col
) sub ON main.id = sub.join_col

这些方法的本质都是给优化器传递“该LEFT JOIN不会改变主查询行数”的明确信号，从而触发连接消除的逻辑，避免不必要的表访问。

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