这个SQL查询是什么意思？

I am finding hard time underestanding this query. I know this gives me all supplier names for suppliers who supply all parts that exist to a project. but only because I found the answers online!

我很难找到这个问题。我知道这给了我供应商的所有供应商名称,供应商提供项目中存在的所有部件。但只是因为我在网上找到了答案!

    select sname 
    from s 
    where not exists (select * 
                      from p 
                      where not exists (select * 
                                        from spj spjx 
                                        where s.sno = spjx.sno and
                                              p.pno = spjx.pno
                                       )
                     );

3 个解决方案

#1

It helps to reformat it:

它有助于重新格式化:

select sname from s                          -- show all supplier names
where not exists                             -- that there is not 
      (select * from p                       -- a part
       where not exists                      -- that is not
             (select * from spj spjx         -- supplied
              where s.sno = spjx.sno         -- by them
                and p.pno = spjx.pno));

Basically: select all sname from s, where no p exists where no spj such that spj matches s and p. Think of each layer as a filter.

基本上:从s中选择所有sname,其中没有p存在,其中spj不匹配s和p。将每个层视为过滤器。

And the result looks like a relational division, as point out by Martin in a comment.

正如马丁在评论中指出的那样,结果看起来像是一个关系师。

#2

You can think of it like sets filtering sets. There are three sets here:

您可以将其视为集过滤集。这里有三组:

select * from spj spjx
where s.sno = spjx.sno and
    p.pno = spjx.pno

select * from p
where not exists ({previous set})

select sname from s
where not exists ({previous set})

So, everywhere you see {previous set}, the outer set is being filtered by the result of that set.

因此,无论您在哪里看到{previous set},外部集都会被该集的结果过滤掉。

Further, for completeness, when you see this:

此外,为了完整性,当你看到这个:

from spj spjx

that's equivalent to:

这相当于:

from spj AS spjx

thus making spjx the alias in this example.

从而使spjx成为此示例中的别名。

#3

Instead of saying
"show all suppliers who supply every part",
it says
"Show all suppliers for whom no part is not supplied by them"

它没有说“显示供应每个部件的所有供应商”,而是说“显示所有供应商,而他们没有供应任何部件”

#1