What does this SQL query mean?

Question

I find it difficult to find this request. I know that this gives me all the names of the suppliers for the suppliers who supply all the parts that exist for the project. but just 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
                                       )
                     );

Answer 1

This helps 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 there is no spj, so spj matches s and p. Think of each layer as a filter.

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

Answer 2

You can think of it as a set of filter 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})

, , {previous set}, .

, , :

from spj spjx

:

from spj AS spjx

spjx alias .

Answer 3

Instead of saying “show all suppliers who supply each part,”
he says “Show all suppliers for which a part is not provided to them”