存在和不存在查询误解？ [英] Exist and Not Exists Query Misunderstanding?

问题描述

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

• 零件 ，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]


                        
这篇关于存在和不存在查询误解？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！