求助：基于双列优先级关联查询的SQL高效实现方案

需求说明 #

需要编写SQL查询从table2中选取列，关联逻辑如下：

• 优先基于table1和table2的qux列匹配（仅当两表的qux均不为NULL时）
• 若qux无匹配结果，则改用quux列关联

现有方案的性能问题 #

当前采用嵌套子查询+COALESCE的方案可以得到正确结果，但在大数据量场景下性能极差：

• 每个需要查询的table2列都要重复编写COALESCE嵌套子查询
• 多次重复扫描table2，导致IO开销极高

现有查询代码：

SELECT t1.foo, t1.bar  ,
    COALESCE(  
    (  
    SELECT top 1 t2.baz 
   FROM  table2 as t2  
    WHERE t2.qux = t1.qux   
    ),  
    (  
    SELECT top 1 t2.baz 
    FROM  table2 as t2  
    WHERE  t2.quux = t1.quux
    )  
    ) AS baz, 
    COALESCE(  
    (  
    SELECT top 1 t2.corge 
   FROM  table2 as t2  
    WHERE t2.qux = t1.qux   
    ),  
    (  
    SELECT top 1 t2.corge 
    FROM  table2 as t2  
    WHERE  t2.quux = t1.quux
    ) ) AS corge  
FROM  table1 as t1

尝试过的无效方案 #

使用INTERSECT/EXCEPT替代COALESCE均无法得到正确结果，示例代码如下：

INTERSECT方案 #

SELECT t1.foo, t1.bar  , 
    (SELECT top 1 t2.baz 
   FROM  table2 as t2  
    WHERE t2.qux = t1.qux
    INTERSECT  
    SELECT top 1 t2.baz 
    FROM  table2 as t2  
    WHERE  t2.quux = t1.quux)
    AS baz,
    (SELECT top 1 t2.corge 
   FROM  table2 as t2  
    WHERE t2.qux = t1.qux   
    INTERSECT
    SELECT top 1 t2.corge 
    FROM  table2 as t2  
    WHERE  t2.quux = t1.quux)
    AS corge
FROM  table1 as t1

EXCEPT方案 #

SELECT t1.foo, t1.bar  , 
    (SELECT top 1 t2.baz 
   FROM  table2 as t2  
    WHERE t2.qux = t1.qux
    EXCEPT  
    SELECT top 1 t2.baz 
    FROM  table2 as t2  
    WHERE  t2.quux = t1.quux)
    AS baz,
    (SELECT top 1 t2.corge 
   FROM  table2 as t2  
    WHERE t2.qux = t1.qux   
    EXCEPT
    SELECT top 1 t2.corge 
    FROM  table2 as t2  
    WHERE  t2.quux = t1.quux)
    AS corge
FROM  table1 as t1

高效优化方案 #

方案1：LEFT JOIN + COALESCE（仅两次关联） #

通过两次LEFT JOIN分别关联qux和quux，再用COALESCE优先取qux匹配的列，仅需扫描table2两次，大幅减少IO开销：

SELECT 
    t1.foo,
    t1.bar,
    COALESCE(t2_qux.baz, t2_quux.baz) AS baz,
    COALESCE(t2_qux.corge, t2_quux.corge) AS corge
FROM table1 t1
-- 优先关联qux（当t1.qux不为NULL时）
LEFT JOIN table2 t2_qux ON t2_qux.qux = t1.qux
-- 仅当qux无匹配时，关联quux
LEFT JOIN table2 t2_quux ON t2_quux.quux = t1.quux 
    AND t2_qux.qux IS NULL -- 确保qux无匹配才走这条关联

方案2：OUTER APPLY + 优先级排序 #

通过OUTER APPLY一次性获取符合优先级的匹配行，逻辑更直观，结合索引优化可实现单次高效扫描：

SELECT 
    t1.foo, 
    t1.bar, 
    t2.baz, 
    t2.corge
FROM table1 t1
OUTER APPLY (
    SELECT TOP 1 t2.baz, t2.corge
    FROM table2 t2
    WHERE 
        -- 匹配条件：要么qux有效且相等，要么qux无效时匹配quux
        (t1.qux IS NOT NULL AND t2.qux = t1.qux)
        OR (t1.qux IS NULL AND t2.quux = t1.quux)
    -- 排序确保qux匹配的结果优先返回
    ORDER BY 
        CASE 
            WHEN t1.qux IS NOT NULL AND t2.qux = t1.qux THEN 1 
            ELSE 2 
        END
) t2

索引优化建议 #

为table2的关联列创建包含查询字段的非聚集索引，进一步提升匹配效率：

-- 针对qux匹配的索引
CREATE NONCLUSTERED INDEX IX_table2_qux ON table2(qux) INCLUDE(baz, corge);
-- 针对quux匹配的索引
CREATE NONCLUSTERED INDEX IX_table2_quux ON table2(quux) INCLUDE(baz, corge);

可复现示例代码 #

表结构定义 #

CREATE TABLE [dbo].[table1](
    [foo] [bigint] NULL,
    [bar] [nvarchar](50) NULL,
    [qux] [bigint] NULL,
    [quux] [nvarchar](50) NULL
); 

CREATE TABLE [dbo].[table2](
    [baz] [nvarchar](50) NULL,
    [qux] [bigint] NULL,
    [quux] [nvarchar](50) NULL,
    [corge] [bigint] NULL
);

测试数据插入 #

INSERT [dbo].[table1] ([foo], [bar], [qux], [quux]) VALUES (1, N'asd', 345, NULL);
INSERT [dbo].[table1] ([foo], [bar], [qux], [quux]) VALUES (2, N'fas', NULL, N'abc');
INSERT [dbo].[table1] ([foo], [bar], [qux], [quux]) VALUES (3, N'fasfdjka', 678, NULL);
INSERT [dbo].[table1] ([foo], [bar], [qux], [quux]) VALUES (4, N'jggiy', NULL, N'def');
INSERT [dbo].[table2] ([baz], [qux], [quux], [corge]) VALUES (N'afsfsaf', 345, N'xyz', 764694659);
INSERT [dbo].[table2] ([baz], [qux], [quux], [corge]) VALUES (N'jjuiku', 8910, N'abc', 519285912);
INSERT [dbo].[table2] ([baz], [qux], [quux], [corge]) VALUES (N'gghsd', 678, N'vuw', 152512512);
INSERT [dbo].[table2] ([baz], [qux], [quux], [corge]) VALUES (N'oolas;p', 111213, N'def', 921839129);

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