NOT EXISTS clause in SQL

Question

I have been stuck on a query and I am really not able to think how does the execution takes place, any help will be highly appreciated :

The query is devised to find the details of the employee who works on all the projects.

The query is :

SELECT E.LNAME, E.FNAME
FROM EMPLOYEE E
WHERE NOT EXISTS
(
    SELECT PNUMBER
    FROM PROJECT
    WHERE PNUMBER NOT EXISTS 
    (
        SELECT PNO 
        FROM WORKS_ON
        WHERE ESSN=E.SSN 
    ) 
);

DB Structure is :

Table Projects with columns :

Pname,Pnumber,Plocation and dnum

Table works_on with columns :

ESSN,PNO and HOURS

Table Employee with columns :

Fname,minit,Lname,SSN,Bdate,address, sex,salary,superssn and dno

If someone can explain in simple words how this query executes it will be really helpful.

Answer 1

The SQL EXISTS condition is considered "to be met" if the subquery returns at least one row.

Therefore, by implying NOT EXISTS, we want the subquery to return zero rows, so with that knowledge let's look at your query

SELECT E.LNAME, E.FNAME
FROM EMPLOYEE E
WHERE NOT EXISTS (SELECT PNUMBER
FROM PROJECT
WHERE PNUMBER NOT EXISTS (SELECT PNO 
FROM WORKS_ON
WHERE ESSN=E.SSN ) );

There are two nested NOT EXISTS statement, and SQL will have to run them in reverse order, because one relies on the other. The first one which will be queried is this one (the last one):

SELECT PNO 
    FROM WORKS_ON
    WHERE ESSN=E.SSN

If this returns zero rows (because we've said NOT EXISTS), then it will run the next query, which will be:

SELECT PNUMBER
    FROM PROJECT

Again, this has to return zero rows, and if it does, then it will run the final query, which is the first one.

SELECT E.LNAME, E.FNAME
FROM EMPLOYEE E

In essence, every "NOT EXIST" subquery has to return zero rows for the preceding query to run, otherwise you will end up with 0 rows (no results).

More information about the EXISTS condition here

Answer 2

I know this is an old question but I was intrigued so I spent some time on it and it would be a waste to let the effort be lost.

First of all, I don't know the syntax of the inner <column name> NOT EXISTS <subquery> but it seems to be equivalent to <column name> NOT IN <subquery>. Just this notion made the query more comprehensive for me because it more clearly links the inner query to the PROJECT.
So I started from

SELECT E.LNAME, E.FNAME
FROM EMPLOYEE E
WHERE NOT EXISTS
(
    SELECT PNUMBER
    FROM PROJECT
    WHERE PNUMBER NOT IN
    (
        SELECT PNO
        FROM WORKS_ON
        WHERE ESSN=E.SSN
    )
);

Breakdown of the steps:

• The inner query simply lists all the project numbers that your employee works on.

          SELECT PNO
          FROM WORKS_ON
          WHERE ESSN=E.SSN
  

• The middle query takes the complement. The result is all the project numbers that your employee does not work on.

      SELECT PNUMBER
      FROM PROJECT
      WHERE PNUMBER NOT IN
      (
          -- projects that the employee works on
      )
  

• If there are projects existing where the employee doesn't work on then he doesn't work on all of them and he should therefor not be included in the results.

  SELECT E.LNAME, E.FNAME
  FROM EMPLOYEE E
  WHERE NOT EXISTS
  (
      -- projects that the employee does not work on
  )