多连接条件下Not Exists查询无结果问题排查及优化需求

需求描述 #

从包含约4万条记录、35列的TEST_TABLE表中筛选符合以下条件的记录：

• 当前记录的UPLIFTED字段为空或不等于"U"
• 存在与当前记录ORDER_ID和ITEM_ID相同，且UPLIFTED字段为"U"的其他记录
• 不存在与当前记录ORDER_ID和ITEM_ID相同，且UPLIFTED字段为"U"、STATUS字段不等于"Shipped"的其他记录

示例数据 #

简化测试数据如下（仅ID=1的记录符合预期结果）：

原查询问题分析 #

你编写的SQL未返回结果，核心问题有两点：

1. NOT EXISTS条件错误：原查询排除了所有ORDER_ID+ITEM_ID分组下存在STATUS≠"Shipped"的记录，但需求仅要求排除分组内存在**UPLIFTED="U"且STATUS≠"Shipped"**的记录，缺少tt3.UPLIFTED = "U"的过滤条件。
2. INNER JOIN导致冗余：直接关联同表会产生重复记录，用EXISTS替代JOIN更简洁高效，可避免不必要的笛卡尔积。

修正后的查询语句 #

方案1：使用EXISTS/NOT EXISTS（逻辑清晰，兼容MS Access） #

SELECT t.*
FROM TEST_TABLE t
WHERE 
    -- 条件1：当前记录UPLIFTED为空或不等于"U"
    (t.UPLIFTED IS NULL OR t.UPLIFTED <> "U")
    -- 条件2：同ORDER+ITEM下存在其他UPLIFTED="U"的记录
    AND EXISTS (
        SELECT 1
        FROM TEST_TABLE t2
        WHERE t2.ORDER_ID = t.ORDER_ID
          AND t2.ITEM_ID = t.ITEM_ID
          AND t2.UPLIFTED = "U"
          AND t2.ID <> t.ID -- 排除当前记录自身，符合"其他记录"的要求
    )
    -- 条件3：同ORDER+ITEM下不存在UPLIFTED="U"且STATUS≠"Shipped"的记录
    AND NOT EXISTS (
        SELECT 1
        FROM TEST_TABLE t3
        WHERE t3.ORDER_ID = t.ORDER_ID
          AND t3.ITEM_ID = t.ITEM_ID
          AND t3.UPLIFTED = "U"
          AND t3.STATUS <> "Shipped"
    );

方案2：分组预筛选（适合大数据量，减少重复扫描） #

先筛选出符合条件的ORDER_ID+ITEM_ID分组，再关联原表获取完整记录：

SELECT t.*
FROM TEST_TABLE t
INNER JOIN (
    SELECT ORDER_ID, ITEM_ID
    FROM TEST_TABLE
    GROUP BY ORDER_ID, ITEM_ID
    HAVING 
        -- 分组内存在UPLIFTED="U"的记录
        SUM(IIF(UPLIFTED = "U", 1, 0)) > 0
        -- 分组内不存在UPLIFTED="U"且STATUS≠"Shipped"的记录
        AND SUM(IIF(UPLIFTED = "U" AND STATUS <> "Shipped", 1, 0)) = 0
) AS valid_groups ON t.ORDER_ID = valid_groups.ORDER_ID AND t.ITEM_ID = valid_groups.ITEM_ID
WHERE (t.UPLIFTED IS NULL OR t.UPLIFTED <> "U");

效率优化建议 #

针对4万条记录的表，建议：

• 创建联合索引：CREATE INDEX idx_order_item ON TEST_TABLE (ORDER_ID, ITEM_ID);
• 若允许，创建覆盖索引减少回表操作：CREATE INDEX idx_order_item_uplifted_status ON TEST_TABLE (ORDER_ID, ITEM_ID) INCLUDE (UPLIFTED, STATUS);

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