不存在的 SQL 双重否定

Question

我有一个嵌套的 Not Exists，想知道它是否是双重否定。如果我删除两个否定，它是否是等效的查询？

Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)

SQL 翻译:“C.Sid 不存在 C.Sid 未提供的部分”

SELECT C.sid
FROM Catalog C
WHERE NOT EXISTS (SELECT P.pid
                  FROM Parts P
                  WHERE NOT EXISTS (SELECT C1.sid
                                    FROM Catalog C1
                                    WHERE C1.sid = C.sid
                                    AND C1.pid = P.pid)
                  )

删除否定:“C.Sid who supply every part”

SELECT C.sid
FROM Catalog C
WHERE EXISTS (SELECT P.pid
                  FROM Parts P
                  WHERE EXISTS (SELECT C1.sid
                                    FROM Catalog C1
                                    WHERE C1.sid = C.sid
                                    AND C1.pid = P.pid)
                  )

Answer 1

不，操作不等价。您要实现的目标相当于 algebra division operation在 SQL 中。

这是一个SQLFiddle您可以在其中测试以下查询:

第一个:

SELECT * FROM Catalog C
WHERE NOT EXISTS (SELECT P.pid FROM Part P
                  WHERE NOT EXISTS (SELECT C1.sid FROM Catalog C1
                                    WHERE C1.sid = C.sid
                                    AND C1.pid = P.pid) );

sid pid
1   1
1   2
2   1
2   2

备选方案(您可以看到结果现在是等效的):

SELECT * FROM Catalog C
WHERE EXISTS (SELECT P.pid FROM Part p
              WHERE EXISTS (SELECT C1.sid FROM Catalog C1
                            WHERE C1.sid = C.sid
                              AND C1.pid = P.pid) );

sid pid
1   1
1   2
2   1
2   2
3   1
3   3

还有一个经典的数据库类(class)练习:

-- Suppliers for which doesn't exists any part that they doesn't provide.
SELECT * FROM supplier S
WHERE NOT EXISTS ( SELECT * FROM part P
                   WHERE NOT EXISTS ( SELECT * FROM catalog C
                                      WHERE S.sid = C.sid
                                        AND P.pid = C.pid ) );

sid name
1   "Dath Vader"
2   "Han Solo"

剖析上述查询的一部分可能会让您更好地了解查询中涉及的逻辑。

SELECT * FROM part P
WHERE NOT EXISTS ( SELECT * FROM catalog C
                   WHERE P.pid = C.pid
                     AND C.sid = 3); -- R2D2 Here!

pid name
2   "Laser Gun"

R2D2 被排除在结果集中，因为它是唯一销售未在零件表中列出的产品的产品。该行的存在从最终结果集中排除了 RD2D。