sql - 不存在的SQL双重否定

我有一个嵌套的“不存在”，并且想知道它是否是双重否定。如果我同时删除这两个否定词，是否也将是等效查询？

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”

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)
                  )

最佳答案

不，操作不等效。您要实现的目标等同于SQL中的algebra division operation。

这是一个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

和经典的数据库课程练习：

-- 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从最终结果集中排除。

关于sql - 不存在的SQL双重否定，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/33833333/