CODEWITHSUNDEEP
sqloracle-databasejoinsubquery
Sergio
Sergio

Reputation: 275

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:

Upvotes: 0

Views: 345

Answers (2)

forpas
forpas

Reputation: 164064

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.

Upvotes: 1

Andrew Sayer
Andrew Sayer

Reputation: 2336

Your query won’t run because you haven’t specified which employee to take name from. Logically, the only thing different with will be that you may get duplicate rows, eg there may be some rows in s that have the same ssn and supervisor_ssn although it seems likely that ssn is a unique key so this wouldn’t happen. There is nothing wrong with using this style of joins.

Oracle is able to optimize both of these queries very well so you shouldn’t worry for the most part - so long as you aren’t relying on the semi-join nature of in (subquery).

Other RDBMSs may not be as clever with their transformations so you may find the first query to force certain execution plans (which may or may not be desirable) but the second will give the optimizer more flexibility.

Upvotes: 1

Related Questions