MySQL - How is this query performing relational division?

Question

I needed to make a query that performs relational division between 2 tables in MySQL. After researching a bit I found that the following query would perform relational division (and it works fine) but no matter how hard I try, I can't figure out how or why it's working.

Here are the 2 tables:

mysql> SELECT * FROM Works_on;
+-----------+-----+-------+
| Essn      | Pno | Hours |
+-----------+-----+-------+
| 123456789 |   1 |  32.5 |
| 123456789 |   2 |   7.5 |
| 333445555 |   1 |   0.0 |
| 333445555 |   2 |  10.0 |
| 333445555 |   3 |  10.0 |
| 333445555 |  10 |  10.0 |
| 333445555 |  20 |  10.0 |
| 333445555 |  30 |   0.0 |
| 453453453 |   1 |  20.0 |
| 453453453 |   2 |  20.0 |
| 666884444 |   3 |  40.0 |
+-----------+-----+-------+

mysql> SELECT * FROM Project;
+-----------------+---------+-----------+------+
| Pname           | Pnumber | Plocation | Dnum |
+-----------------+---------+-----------+------+
| ProductX        |       1 | Bellaire  |    5 |
| ProjectY        |       2 | Sugarland |    5 |
| ProjectZ        |       3 | Houston   |    5 |
| Computerization |      10 | Stafford  |    4 |
| Reorganization  |      20 | Houston   |    1 |
| Newbenefits     |      30 | Stafford  |    4 |
+-----------------+---------+-----------+------+

SQL Fiddle

The problem: To find the Essn of employees that work on all the projects listed in the Project table. So basically Works_on divided by Project on the basis of Pnumber.

The query I wrote is:

mysql> SELECT DISTINCT Essn FROM Works_on w1 
WHERE NOT EXISTS 
(SELECT * FROM Project p 
    WHERE NOT EXISTS 
    (SELECT * FROM Works_on w2
        WHERE w2.Essn = w1.Essn AND w2.Pno = p.Pnumber));
+-----------+
| Essn      |
+-----------+
| 333445555 |
+-----------+

Please help me understand how this query is working. In particular, the innermost query puzzles me.

Answer 1

You know, I would actually stick with this:

SELECT Essn
   FROM Works_on 
 WHERE Pno IN (SELECT Pnumber 
                 FROM Project)
GROUP BY 
       Essn
HAVING COUNT(*) = (SELECT COUNT(*)
                 FROM Project);

I believe there is some perfomance advantage (against your query), and a clear logic structure as well.

SQL Fiddle

P.S. Side advice - if you want to understand the logic of something, just try do it yourself. Even if you won't understand - there is a good chanche you will just find another solution(and probably a better one)