如何用WHERE NOT EXISTS实现四表除法查询：找出购买全部商品的客户

已知三表关联除法查询模板 #

适用于Customers、Products、Purchases三表关联场景，查询购买全部商品的客户：

SELECT *
FROM Customers AS A
WHERE NOT EXISTS
(
    SELECT *
    FROM Products AS B
    WHERE NOT EXISTS
    (
        SELECT *
        FROM Purchases AS C
        WHERE C.CustomerID= A.CustomerID
        AND C.ProductID= B.ProductID
    )
);

四表结构说明 #

涉及四张表：

• Customers：主键为CustomerID
• Products：主键为ProductID
• Invoices：主键为InvoiceID，外键为CustomerID（关联Customers）
• InvoiceLines：外键为InvoiceID（关联Invoices）和ProductID（关联Products）

需通过Invoices和InvoiceLines关联CustomerID与ProductID，找出购买了全部商品的客户（正确结果应为CustomerID=3的客户），但以下两种查询均返回所有客户，无法得到正确结果：

第一种错误查询 #

SELECT *
FROM Customers AS C
WHERE NOT EXISTS
(
    SELECT *
    FROM Products AS P
    WHERE NOT EXISTS
    (
        SELECT *
        FROM Invoices AS I
        WHERE NOT EXISTS
            (
                SELECT *
                FROM InvoiceLines AS L
                WHERE I.CustomerID= C.CustomerID
                AND L.InvoiceID= I.InvoiceID
                AND L.ProductID= P.ProductID
            )
    )
);

第二种错误查询 #

SELECT *
FROM Customers AS C
WHERE NOT EXISTS
(
    SELECT *
    FROM Products AS P
    WHERE NOT EXISTS
    (
        SELECT *
        FROM 
            (
            SELECT * FROM InvoiceLines AS L, Invoices AS I
            WHERE  L.InvoiceID= I.InvoiceID
            ) AS Pu
        WHERE Pu.CustomerID= C.CustomerID
        AND Pu.ProductID= P.ProductID
        )
    );

修正后的查询方案 #

核心是在最内层直接关联Invoices和InvoiceLines，验证客户是否购买了指定商品，符合除法查询"不存在未购买商品"的核心逻辑：

SELECT *
FROM Customers AS C
WHERE NOT EXISTS
(
    SELECT *
    FROM Products AS P
    WHERE NOT EXISTS
    (
        SELECT *
        FROM Invoices AS I
        JOIN InvoiceLines AS L ON I.InvoiceID = L.InvoiceID
        WHERE I.CustomerID = C.CustomerID
          AND L.ProductID = P.ProductID
    )
);

错误原因说明 #

1. 第一种错误查询多嵌套了一层NOT EXISTS：最内层的NOT EXISTS会反转逻辑——只要存在任何一张发票未对应目标商品，就会返回true，最终导致外层逻辑失效，返回所有客户。
2. 第二种错误查询的子查询Pu虽关联了两张表，但未正确过滤"客户-商品"的对应关系，当客户存在未购买的商品时，NOT EXISTS判断无法生效，错误返回所有客户。

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