Understanding NOT EXISTS in SQL

Question

I am a beginner in SQL. I am now studying EXISTS and NOT EXISTS.

Question:

Retrieve the names of each employee who works on ALL projects controlled by department 5.

Answer:

select fname, lname
from employee
where not exists ( (select pnumber from project where dnum = 5)
                   MINUS 
                   (select pno from works_on where essn = ssn)
                 );

The first select in subquery gives (1,2,3) and the second select gives me (1,2,3,10,20,30). So (1,2,3) - (1,2,3,10,20,30) = 0. How does this solve the query? I am not sure if my logic is correct or the way I approach the problem.

Can someone please help me understand the solution step by step and visually if possible? thanks

Link to the course

Answer 1

So, the original problem was to:

Retrieve the names of each employee who works on ALL the projects controlled by department 5.

The provided answer makes use of the equivalence of:

1. All x such that f(x)
2. No x such that not f(x)

To put that in English, the problem is equivalent to finding those employees for whom there is no project controlled by department 5 that the employee doesn't work on.

So, first find all the projects controlled by department 5, then remove from that any project that the employee works on. That's exactly what the provided answer is doing. If there is nothing left, then there is no project controlled by department 5 that the employee doesn't work on. So by the equivalance, the employee works on all the projects controlled by that department.

While this is technically correct, it can feel a little odd. Especially if it were the case that department 5 controls zero projects. If that were true, the query would return all the employees ... which might not be quite what was expected.