Search code examples
sqloracle-databasejoinsubquery

Is there any advantages of using nested queries when not necessary in Oracle SQL?


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:

  • Is there any reason I should resort to their given method if I have a similar query?
  • IS there any advantage to using nested queries when not needed?

Solution

  • 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_ssns 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.