LEFT JOIN与WHERE语句的最佳替代方案？大表CTE场景查询优化

首先明确你要的结果：保留两类用户——买过Guitar Type A的用户（返回他们所有购买记录），以及完全没有任何购买记录的用户；同时排除有购买但从未买过Guitar Type A的用户。你的现有代码没能覆盖这些需求，我来给你调整并优化性能：

优化后的CTE查询代码 #

WITH CustomersWithTypeA AS (
    -- 先提取所有买过Guitar Type A的用户ID，减少后续大表关联的开销
    SELECT DISTINCT CustId
    FROM Purchase
    WHERE GuitarType = 'A'
),
RelevantCustomers AS (
    -- 筛选出符合条件的目标用户：要么买过A，要么无任何购买记录
    SELECT c.CustId, c.CustType
    FROM Customer c
    LEFT JOIN CustomersWithTypeA cta ON c.CustId = cta.CustId
    WHERE 
        cta.CustId IS NOT NULL -- 买过Guitar Type A的用户
        OR NOT EXISTS ( -- 完全没有购买记录的用户
            SELECT 1 FROM Purchase p WHERE p.CustId = c.CustId
        )
),
CustomerPurchaseRecords AS (
    -- 关联获取目标用户的所有购买记录（无购买的用户会返回NULL）
    SELECT 
        rc.CustId,
        p.GuitarType,
        p.PurchaseDate
    FROM RelevantCustomers rc
    LEFT JOIN Purchase p ON rc.CustId = p.CustId
)
SELECT * FROM CustomerPurchaseRecords;

代码逻辑说明 #

1. CustomersWithTypeA：通过DISTINCT快速拿到所有买过A类吉他的用户ID，这里如果给Purchase表建(GuitarType, CustId)的复合索引，能直接走索引查询，避免扫描整个大表。
2. RelevantCustomers：从用户表中筛选出两类目标用户：要么在买过A的列表里，要么完全没有购买记录。用NOT EXISTS判断无购买记录比LEFT JOIN后判NULL更高效，尤其是当Purchase表数据量极大时。
3. CustomerPurchaseRecords：把目标用户和他们的购买记录做LEFT JOIN，这样无购买的用户会返回NULL的吉他类型和购买日期，完美匹配你的需求。

性能优化关键点 #

• 给Purchase表创建复合索引：CREATE INDEX idx_purchase_guitar_cust ON Purchase(GuitarType, CustId);，这会让第一个CTE的查询速度大幅提升。
• 给Purchase表单独建CustId的索引：CREATE INDEX idx_purchase_custid ON Purchase(CustId);，加速NOT EXISTS的子查询以及最后的关联操作。

测试数据验证 #

用你提供的测试数据：

• Customer表有CustId 1、2、4
• Purchase表中，CustId1买过A和C；CustId2买过A和B；CustId4无购买记录

最终查询结果会是：

完全符合你的要求：包含买过A的用户的所有购买记录，以及无购买的用户，排除了有购买但没买A的用户（如果存在这类用户的话）。

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