基于参数返回左连接表的全部/匹配/不匹配行的优化咨询

嘿，我来帮你优化这个根据参数筛选左连接结果的SQL查询！你的原实现用了临时表和CASE转换，确实在大表场景下容易因为额外的计算和子查询拖慢性能。下面是几种更高效、简洁的实现方式：

方案一：直接参数化WHERE条件 #

这种方式去掉了临时表和CASE转换，让查询优化器能直接利用索引生成最优执行计划：

DECLARE @SalesType VARCHAR(10) = 'All';

SELECT sales.*
FROM sales
LEFT JOIN esales ON sales.pk = esales.fk
WHERE 
    (@SalesType = 'All')
    OR (@SalesType = 'No ESales' AND esales.fk IS NULL)
    OR (@SalesType = 'ESales' AND esales.fk IS NOT NULL);

优势：代码简洁，没有多余的中间步骤。SQL Server的查询优化器可以根据@SalesType的具体值，直接选择对应的过滤逻辑，能很好地利用sales.pk和esales.fk上的索引，大表场景下性能提升明显。

方案二：添加RECOMPILE避免参数嗅探 #

如果不同参数对应的数据量差异很大（比如'All'返回百万行，'No ESales'只返回几十行），可以加上OPTION (RECOMPILE)让每次执行都生成适配当前参数的最优计划：

DECLARE @SalesType VARCHAR(10) = 'All';

SELECT sales.*
FROM sales
LEFT JOIN esales ON sales.pk = esales.fk
WHERE 
    (@SalesType = 'All')
    OR (@SalesType = 'No ESales' AND esales.fk IS NULL)
    OR (@SalesType = 'ESales' AND esales.fk IS NOT NULL)
OPTION (RECOMPILE);

优势：避免了执行计划缓存带来的性能退化问题，确保每次查询都用最适合当前参数的执行计划。

方案三：IF ELSE分支针对性查询 #

这种方式把不同参数的逻辑拆分成独立的查询分支，每个分支都是最贴合需求的最优写法：

DECLARE @SalesType VARCHAR(10) = 'All';

IF @SalesType = 'All'
BEGIN
    SELECT sales.*
    FROM sales
    LEFT JOIN esales ON sales.pk = esales.fk;
END
ELSE IF @SalesType = 'No ESales'
BEGIN
    SELECT sales.*
    FROM sales
    LEFT JOIN esales ON sales.pk = esales.fk
    WHERE esales.fk IS NULL;
END
ELSE IF @SalesType = 'ESales'
BEGIN
    -- 这里用INNER JOIN替代LEFT JOIN+过滤，减少不必要的行扫描
    SELECT sales.*
    FROM sales
    INNER JOIN esales ON sales.pk = esales.fk;
END

优势：每个分支的查询都是针对性优化的，比如当参数为'ESales'时，用INNER JOIN直接获取匹配行，比LEFT JOIN后再过滤效率更高。而且每个分支的执行计划都是独立优化的，适合数据量差异大的场景。

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