存在和不存在查询误解？

本文介绍了存在和不存在查询误解？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

请考虑以下产品数据库模式：

• 零件 ，pname）
• 供应商（sid，sname）
• 目录（sid，pid）

sid 目录是外键，引用供应商和 pid 目录是外键，引用 Parts 表。 在中显示供应商（s1，p1）产生 p1 。

如果我想查找 >那些不是由一些供应商提供的，我们怎么能这样做？我读了一些部分如下：

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

我的问题是通过 - 为什么？

解决方案

如果我想找到零件的细节不是由某些供应商提供，我们如何做？

如果您想要存在不提供该零件的供应商的零件的详细信息，则使用 EXISTS ，然后 NOT EXISTS ，是正确的。

我的问题是通过 - 我们使用存在和第二...我们使用不存在？

我将解释为什么不提供该部分可以提供您的查询。 （ EXISTS ，然后 NOT EXISTS 。）

A表格保留了使谓词（语句模板）成为真实命题（语句）的行：

• 零件零件P.pid名为P.pname
• / code>供应商S.sid命名为S.sname
• 目录 .pid

您需要为结果表达谓词。您想要从中创建真实语句的 P.pid - P.pname 行：

部分P.pid命名为P.pname AND EXISTS S.sid [supplier S.sid命名为 AND NOTsupplier S.sid supplies part P.pid]

但我们必须表达该谓词根据给定的谓词（加上条件和逻辑运算符），以便DBMS可以计算满足它的行：

部分P.pid命名为P.pname和EXISTS S.sid [EXISTS S.sname供应商S.sid命名为S.sname AND NOT EXISTS C.sid，C.pid [supplier C.sid supplies part P.pidAND C.sid = S.sid AND C.pid = P.pid]]

现在转换为SQL：

• em> 的谓词变成 FROM T b $ b
• AND 的谓词变为 JOIN T / em> c> c $> code> 或 ON 条件
• 最外面的EXISTS 已删除的列变为 SELECT 的所有列 [ 的谓词变成 EXISTS / code> c> / ul>
  SELECT * FROM零件P WHERE EXISTS（SELECT S.sid FROM S WHERE NOT EXISTS（SELECT 1 FROM Catalog C WHERE C.sid = S.sid AND C.pid = P.pid））

  您的问题的原始部分供应商不提供的零件详细资料 /暧昧。这可能意味着 P.id - P.pname 对其中：

• 零件不是由所有供应商提供（解释同意您的SQL）
• 不提供零件任何供应商）（解释同意 Shnugo的回答中的两个SQL版本）

• 零件没有多个供应商

  SELECT * FROM零件P WHERE NOT EXISTS（SELECT * FROM目录C1 JOIN目录C2 ON C1.sid<> C2.sid AND C1.pid = P.pid AND C2.pid = P.pid）

如果您有 code> 1-4和供应商 1-3，然后上面给出三个不同的答案：

INSERT INTO目录VALUES（1,1），（2,1），（2,2），（3,1），（3,2），（3,3） b $ b

PPS 上述逻辑公式为：

- 1.零件不是由所有供应商提供 P（P.pid，P.pname）和现有的S.sid，S.sname [S（S.sid，S.sname）AND NOT C（S.sid，P.pid）] - 2零件不提供供应商） P（P.pid，P.pname）AND NOT EXISTS C.sid C（C.sid，P.pid） - 3.零件没有多个供应商 P（P.pid，P.pname） AND NOT EXISTS C1.sid，C2.sid [C（C1.sid，P.pid）AND C sid，P.pid）和C1.sid C2.sid]

Consider the following schema of a product database:

• Parts (pid, pname)
• Suppliers (sid, sname)
• Catalog (sid, pid)

sid in Catalog is a foreign key and references Suppliers and pid in Catalog is a foreign key and references the Parts table. (s1, p1) in Catalog table shows supplier s1 produces p1.

If I want find details of Parts that are not supplied by some suppliers, how we can do this? I read some parts as follows:

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

My problem is via - why at first? We use exist and in second ... we use not exist?

解决方案

If I want find details of Parts that are not supplied by some suppliers, how we can do this?

If you want "details for parts where there exists a supplier that doesn't supply that part" then your query, with EXISTS then NOT EXISTS, is correct.

My problem is via - why at first? We use exist and in second ... we use not exist?

I will explain why "details for parts where there exists a supplier that doesn't supply that part" can give your query. (With EXISTS then NOT EXISTS.)

A table holds that rows that make some predicate (statement template) into a true proposition (statement):

• Parts "part P.pid is named P.pname"
• Suppliers "supplier S.sid is named S.sname"
• Catalog "supplier C.sid supplies part C.pid"

You need to express the predicate for your result. You want the P.pid-P.pname rows that make a true statement from this:

"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"]

But we must express this predicate in terms of the given predicates (plus conditions and logic operators) so that the DBMS can calculate the rows that satisfy it:

"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]]

Now convert to SQL:

• initial predicate of T becomes FROMT
• AND predicate of T becomes JOINT
• AND condition becomes WHEREcondition or ONcondition
• outermost EXISTS dropped columns becomes SELECTkept columns
• other EXISTS all columns of T [predicate of T] becomes 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 Your question's original "details of parts that are not supplied by some suppliers" is unclear/ambiguous. It could mean P.id-P.pname pairs where:

• "the parts are not supplied by all the suppliers" (the interpretation agreeing with your SQL)
• "the parts are not not supplied (by any of the suppliers)" (the interpretation agreeing with the two SQL versions in Shnugo's answer

• "the parts don't have multiple suppliers"

  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)

If you have Parts 1-4 and Suppliers 1-3 then the above give three different answers after:

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

PPS Logic formulations for the above are:

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