存在和不存在查询误解

Question

考虑以下产品数据库架构：

• Parts(pid，pname(
• Suppliers(SID，sname(
• Catalog(SID，PID(

Catalog 中的sid是外键，引用Suppliers，Catalog 中的pid是外键，引用Parts表。 Catalog表中(s1, p1)显示了供应商s1生产p1。

如果我想找到某些供应商未提供的Parts的详细信息，我们如何做到这一点？我阅读了一些部分如下：

SELECT *
FROM parts P
WHERE ....... (SELECT S.sid 
               FROM suppliers 
                WHERE .....
                       (SELECT * 
                        FROM catalog C 
                        WHERE s.sid = C.sid AND P.pid = C.pid))

我的问题是通过 - 为什么一开始？我们使用存在和第二...我们使用不存在？

Answer 1

如果我想找到一些供应商不提供的Parts的详细信息，我们如何做到这一点？

如果您想要"存在不提供该零件的供应商的零件的详细信息"，那么您的查询(带有EXISTS然后NOT EXISTS(是正确的。

我的问题是通过 - 为什么一开始？我们使用存在和第二...我们使用不存在？

我将解释为什么"存在不提供该零件的供应商的零件的详细信息"可以为您提供查询。(用EXISTS然后NOT EXISTS。

一个表保存使某些谓词(语句模板(成为真命题(语句(的行：

• Parts "部分 P.pid 被命名为 P.pname">
• Suppliers "供应商 S.sid 被命名为 S.sname">
• Catalog "供应商 C.sid 供应零件 C.pid">

您需要表达结果的谓词。您需要P.pid - P.pname行，从中做出真实的陈述：

    "part P.pid is named P.pname"
AND EXISTS S.sid ["supplier S.sid is named something"
        AND NOT "supplier S.sid supplies part P.pid"]

但是我们必须用给定的谓词(加上条件和逻辑运算符(来表达这个谓词，以便 DBMS 可以计算满足它的行：

    "part P.pid is named P.pname"
AND EXISTS S.sid [EXISTS S.sname "supplier S.sid is named S.sname"
        AND NOT EXISTS C.sid, C.pid [
                "supplier C.sid supplies part P.pid" AND C.sid = S.sid AND C.pid = P.pid]]

现在转换为 SQL：

• T 的初始谓词变为FROM T
• T 的 AND 谓词变为JOIN T
• AND 条件变为WHERE condition或ON condition
• 最外层的 EXISTS 删除的列变为SELECT kept columns
• 其他存在 T 的所有列 [T 的谓词] 都变为EXISTS ( T )

SELECT *
FROM Parts P
WHERE EXISTS (SELECT S.sid FROM Suppliers S
        WHERE NOT EXISTS (SELECT 1 FROM Catalog C
               WHERE C.sid = S.sid AND C.pid = P.pid))

PS 您的问题的原始"某些供应商未提供的零件的详细信息"不清楚/模棱两可。这可能意味着P.id - P.pname对，其中：

1. "零件并非由所有供应商提供"(与您的SQL一致的解释(
2. "零件不是(由任何供应商提供的("(解释与Shnugo回答中的两个SQL版本一致

3. "零件没有多个供应商">

   SELECT * FROM Parts P
   WHERE NOT EXISTS (SELECT *
       FROM Catalog C1 JOIN Catalog C2 ON C1.sid <> C2.sid
       AND C1.pid = P.pid AND C2.pid = P.pid)
   

如果您有 1-4 和 1-3 Parts Suppliers，那么上面给出三个不同的答案：

INSERT INTO Catalog VALUES (1,1),(2,1),(2,2),(3,1),(3,2),(3,3);

PPS逻辑配方包括：

-- 1. "the parts are not supplied by all the suppliers"  
    P(P.pid, P.pname)
AND EXISTS S.sid, S.sname [S(S.sid, S.sname) AND NOT C(S.sid, P.pid)]
-- 2 "the parts are not supplied (by some of the suppliers)"
P(P.pid, P.pname) AND NOT EXISTS C.sid C(C.sid, P.pid)
-- 3. "the parts don't have multiple suppliers"
    P(P.pid, P.pname)
AND NOT EXISTS C1.sid, C2.sid
        [C(C1.sid, P.pid) AND C(C2.sid, P.pid) AND C1.sid <> C2.sid]

Answer 2

不是这么简单吗？如果没有，请使用我的示例表并指定您的预期输出：

DECLARE @Parts TABLE(pid INT, pname VARCHAR(100));
DECLARE @Suppliers TABLE(sid INT, sname VARCHAR(100));
DECLARE @Catalog TABLE(pid INT, sid INT);
INSERT INTO @Parts VALUES(1,'Part 1'),(2,'Part 2'),(3,'Part 3'),(4,'Part 4');
INSERT INTO @Suppliers VALUES(1,'Suppl 1'),(2,'Suppl 2'),(3,'Suppl 3');
INSERT INTO @Catalog VALUES(1,1),(1,3) --part 1 from two suppliers
                          ,(2,1)       --part 2 from one supplier
                                       --part 3 no supplier
                                       --part 4 no supplier
--Get all parts *that not supplied by some suppliers*
SELECT * 
FROM @Parts AS p 
WHERE p.pid NOT IN(SELECT c.pid FROM @Catalog AS c) 

结果

pid pname
3   Part 3
4   Part 4

编辑：使用"您的"查询达到相同的效果，但这会表现得更糟：

此查询正在查找没有供应商在"目录">中具有条目的零件。我必须承认，这闻起来有点像家庭作业：-(

SELECT * FROM @Parts P 
WHERE NOT EXISTS (SELECT S.sid 
                  FROM @Suppliers S 
                  WHERE EXISTS (SELECT 1 
                                FROM @Catalog C 
                                WHERE s.sid = C.sid AND P.pid = C.pid
                                )
                  )