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
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