Understanding NOT EXISTS in SQL
I am a beginner in SQL. Now I am studying EXISTING AND NOT EXISTING.
Question:
Get the names of every employee working 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 the 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 to resolve this request? I'm not sure if my logic is correct or how I approach the problem.
Can someone please help me understand the solution step by step and visually if possible? thank
source to share
So the original problem was this:
Get the names of each employee who works on ALL projects controlled by the department 5.
The provided answer uses the equivalence:
- All x such that f (x)
- There is no x that is not f (x)
In English speaking, the problem is equivalent to finding those employees for whom there is no project controlled by department 5 that is not working as an employee.
So, first find all the projects controlled by department 5, then remove from them any project the employee is working on. This is exactly what the provided answer gives. If there is nothing left, then there is no project controlled by department 5 that the employee is not working on. Thus, by equivalence, the employee works on all projects controlled by this department.
While this is technically correct, it might sound a little odd. Especially if it were the case that department 5 was overseeing zero projects. If that were the case, the request would return all employees ... which might not be quite what was expected.
source to share
First MINUS
is the term Oracle. SQL-Server uses EXCEPT
. Please read https://blog.sqlauthority.com/2008/08/07/sql-server-except-clause-in-sql-server-is-similar-to-minus-clause-in-oracle/ for more information.
Second, it EXISTS
returns a result TRUE
or FALSE
based on whether the parameter operator returns any records. For example, EXISTS ( SELECT * FROM Employee WHERE Fname = 'John' )
will return TRUE
, whereas it EXISTS ( SELECT * FROM Employee WHERE Fname = 'Slartibartfast' )
will return FALSE
.
https://www.w3schools.com/sql/sql_exists.asp gives a good explanation EXISTS
(with examples).
The subquery EXISTS
does not have to refer to the main operator - it just needs to return at least one record under the same circumstances as when it was returned EXISTS
TRUE
. For example...
SELECT *
FROM Employee
WHERE Dno = 5
AND EXISTS ( SELECT Sex
FROM employee
WHERE Sex = 'M'
AND Dno = 5 )
This query will return all records from Employee
(regardless of them Sex
) for Dno
5
if it Department
has at least one person s Sex = 'M'
.
As for NOT EXISTS
...
SELECT *
FROM Employee
WHERE Dno = 5
AND NOT EXISTS ( SELECT Sex
FROM employee
WHERE Sex = 'M'
AND Dno = 5 )
This query will return all records from Employee
(regardless of Sex
) for Dno
5
, if it Department
doesn't have a person with Sex = 'M'
.
If you have any questions or comments, please do not hesitate to post a comment.
source to share