I have a table EMPLOYEE and have the following attributes in it: Name, Ssn, Super_Ssn where Super_Ssn is the supervisor's ssn, I have the following query to write:
Retrieve the names of all employees whose supervisor’s supervisor has ‘888665555’ for Ssn.
The given solution:
SELECT
Name
FROM
EMPLOYEE
WHERE
Super_ssn IN ( SELECT SSN FROM EMPLOYEE WHERE SUPER_SSN=‘888665555’ )
While I wrote the following:
SELECT
Name
FROM
EMPLOYEE E,
EMPLOYEE S,
EMPLOYEE SS
WHERE
E.Super_ssn = S.Ssn
AND
S.Super_ssn = SS.Ssn
AND
ss.Ssn=‘888665555’
My question is the following:
In this query:
SELECT Name
FROM EMPLOYEE
WHERE Super_ssn IN (SELECT SSN FROM EMPLOYEE WHERE SUPER_SSN = '888665555')
there is no nested subquery, because:
SELECT SSN FROM EMPLOYEE WHERE SUPER_SSN = '888665555'
is not nested inside another one.
Also it is not a correlated subquery, so I would expect that it is executed only once and its resultset will be used in the WHERE
clause so that all Super_ssn
s of the table are compared against its values.
So the given solution is a fairly simple readable query.
Your query has its flaws.
You use an outdated syntax for joins and you are doing 2 joins although only 1 is needed.
You could write your query like:
SELECT e.Name
FROM EMPLOYEE e INNER JOIN EMPLOYEE s
ON s.Ssn = e.Super_ssn
WHERE s.Super_ssn = '888665555'
This is also a simple query and readable if you know how joins work.