0

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?
forpas
  • 160,666
  • 10
  • 38
  • 76
Sergio
  • 275
  • 1
  • 15
  • 5
    The **actual problem** with your own solution is that you're using the deprecated (and terrible) old-style JOIN syntax. You should use the modern (well, "modern" meaning 30-years-old) explicit JOIN syntax. https://stackoverflow.com/questions/128965/is-there-something-wrong-with-joins-that-dont-use-the-join-keyword-in-sql-or-my – Dai Dec 19 '20 at 12:52
  • 1
    1) They are not equivalent 2) Old syntax. When using `JOIN` you may get duplicates due to 1:m relationship(depending of data) – Lukasz Szozda Dec 19 '20 at 12:52
  • @Dai I learned the different types of joins but I found this to be the easiest to write which is why I am using it, thank you for sharing that link – Sergio Dec 19 '20 at 12:55
  • @LukaszSzozda can you please point out what's wrong with my own solution? – Sergio Dec 19 '20 at 12:55
  • 2
    @Sergio I am not saying it is wrong. I am pointing out that `JOIN` vs `IN` could lead to different results. In general they are not equivalent. Please preapare sample data https://dbfiddle.uk – Lukasz Szozda Dec 19 '20 at 12:56
  • and what about the logic? – Sergio Dec 19 '20 at 12:57
  • From a technical perspective, your version has one more join than the query provided, so I would prefer that one. – William Robertson Dec 19 '20 at 17:37

2 Answers2

1

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.

forpas
  • 160,666
  • 10
  • 38
  • 76
  • I need the supervisor’s supervisor ssn to be ‘888665555’ , I think your answer gives the supervisor's ssn only – Sergio Dec 19 '20 at 13:21
  • @Sergio `s.Super_ssn` returns the supervisor's supervisor. – forpas Dec 19 '20 at 13:22
  • 1
    oh I just noticed (sorry not familiear with inner join), thank you! – Sergio Dec 19 '20 at 13:23
  • @forpas I'm curious what your definition of "nested" is. IIRC, the ISO SQL spec doesn't strictly define that term. – Dai Dec 19 '20 at 14:03
  • 1
    @Dai I consider a subquery as nested if it is *nested* inside another subquery. If not, then it is just a subquery. – forpas Dec 19 '20 at 14:05
  • @forpas That's a tautological answer: It doesn't define "nested" without using the word "nested". Can you define it in terms of ISO SQL's formal grammar? – Dai Dec 19 '20 at 14:06
  • 1
    It would be *tautological* if I defined "nested subquery" by using "nested subquery". I defined "nested subquery" with the use of the English word "nested". These are 2 different things. – forpas Dec 19 '20 at 14:10
  • 1
    @forpas At risk of seeming argumentative I'll say that I don't find that definition helpful at all because you said the `SELECT SSN FROM EMPLOYEE` part of `... WHERE Super_ssn IN ( SELECT SSN FROM ...) ` is not a nested subquery[1], but using the plain-English-language definition, **it is** a "nested" query because it's a `SELECT` nested in a `WHERE` nested inside a `SELECT`. [1] You wrote "there is no nested subquery, because [..] is not nested inside another one." - how is having a `SELECT` inside another `SELECT` not "nesting" (in the English-language sense of the word)? – Dai Dec 19 '20 at 14:33
  • If you mean the child (nested) query must be inside the **`SELECT`-clause** of a `SELECT`-statement instead of the `WHERE`-clause of a `SELECT`-statement, then that would be a better definition than using the (admittantly) overloaded term "nested". – Dai Dec 19 '20 at 14:36
  • It is not nested in a WHERE. It is used in a WHERE. Do you think that all subqueries in a SQL statement are *nested* subqueries? I don't. – forpas Dec 19 '20 at 14:36
  • 1
    @forpas "It is not nested in a WHERE. It is used in a WHERE" - I'm sorry but I don't see any distinction between the two. What makes something "nested" then? If something is contained within something else then it's "nested", no? If not, then please be more precise in your definition of "nested" without resorting to overloaded and ambiguous terminology. – Dai Dec 19 '20 at 14:37
  • Why don't you post an example of a subquery in a SELECT statement that is not nested? – forpas Dec 19 '20 at 14:39
  • @forpas I can't because by my understanding (and definition of "nested"): "**all** subqueries are nested", but not all nested queries are subqueries, and not all subqueries are correlated-subqueries. – Dai Dec 19 '20 at 15:17
  • @forpas This comment discussion is getting silly - I don't know why you can't simply cite the relevant sections of the ISO SQL spec to back-up your definition of "nested". Heck, I'll also settle for a link to a publicly-accessible page in Oracle, MS SQL, or MySQL's documentation that uses the same terminology. **Semantics is important**. – Dai Dec 19 '20 at 15:17
  • *by my understanding ...* fine, *all subqueries are nested* not so fine, *I don't know why you can't simply cite the relevant sections of the ISO SQL spec to back-up your definition of "nested"* maybe because there is not or I don't know of such a spec and my definition is only my definition. If you have an ISO SQL spec definition that backs up your understanding then post it. Just because I was curious about my understanding I found this: "Nested subqueries : Subqueries are placed within another subquery." in https://www.w3resource.com/sql/subqueries/understanding-sql-subqueries.php – forpas Dec 19 '20 at 15:29
  • 2
    I was rather surprised to find in the [Oracle 19c SQL Reference](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Using-Subqueries.html): *A subquery in the `WHERE` clause of a `SELECT` statement is also called a **nested subquery**. You can nest up to 255 levels of subqueries in a nested subquery.* I would normally assume that *nested subquery* meant that it was within another subquery. If you can just refer to any subquery as a nested subquery then the term is rather meaningless, or at least tautological. *Nested query* is not a term used by Oracle though. – William Robertson Dec 19 '20 at 17:16
  • @WilliamRobertson I agree. So by that definition all subqueries must be considered as nested subqueries and the term *nested* is redundant. – forpas Dec 19 '20 at 17:28
1

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.

Andrew Sayer
  • 2,296
  • 1
  • 7
  • 9
  • Yes ssn is a primary key, and how doesn't my WHERE clause specify which employee's name to choose?\ – Sergio Dec 19 '20 at 13:29
  • Not your where clause, but your select clause. It just says `select name`. Try running it yourself and see the error – Andrew Sayer Dec 19 '20 at 13:30