1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Subquery not working

Discussion in 'SQL PL/SQL' started by mukulverma2408, Jul 19, 2015.

  1. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    how does a subquery works in oracle, the inner query calculates first or the outer one?

    This is my table :
    Code (Text):

    emp_name    emp_id    emp_manager
    john          1                2
    sean          2             NULL
    paul           3             2
    kent           4              2
    peter          5              3
    fiona          6              3
     
    I am using below query to get the employees who do not manages anyone, but there are no rows selected in the o/p
    Code (Text):

    select emp_name from emp where emp_id not in (select emp_manager from emp);
     
    however below is working perfectly as expected :
    Code (Text):

    select emp_name from emp where emp_id not in (2,3);
     
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    >how does a subquery works in oracle, the inner query calculates first or the outer one?

    The innermost subquery is always processed first and the SQL engine works outward from there.

    Your two sample queries are not equivalent. The subquery does not return only 2 and 3. It returns 2 and 3 and NULL. If you try using those three values in your hard-coded IN-list, you'll get the same results as the subquery:

    Code (Text):

    SELECT *
    FROM   temp_emp;

    EMP_NAME       EMP_ID EMP_MANAGER
    ---------- ---------- -----------
    john                1           2
    sean                2          
    paul                3           2
    kent                4           2
    peter               5           3
    fiona               6           3

    SELECT emp_name
    FROM   temp_emp
    WHERE  emp_id NOT IN (2, 3, NULL);

    no rows selected
    When a NULL is compared to a non-NULL value, the result is not FALSE but rather NULL, which basically can be defined as "I don't know". Employees 2 & 3 are removed from the results because 2=2 and 3=3. Employees 1, 4, 5, and 6 are removed because those values compared to NULL result in the SQL engine saying "I don't know". If you remove the NULL values from your subquery, it will return the expected results:

    Code (Text):

    SELECT emp_name
    FROM   temp_emp
    WHERE  emp_id NOT IN (SELECT emp_manager
                          FROM   temp_emp
                          WHERE  emp_manager IS NOT NULL);
                       
    EMP_NAME
    ----------
    fiona    
    peter    
    kent    
    john  
     
    jagadekara likes this.
  3. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    Thanks Matthew, it was very useful... :)
     
  4. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    If it is correlated sub query, then outer query will execute firs, and based on output of outer query inner query will execute.
     
    mukulverma2408 likes this.
  5. mukulverma2408

    mukulverma2408 Active Member

    Messages:
    38
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    New delhi
    What exactly is meant by correlated sub query? is it when the query and sub-query both works on same table?

    is this a correlated subquery?
    Code (Text):

    select max(salary) from tempx where salary < (select max(salary) from tempx);
     
     
    Last edited: Jul 20, 2015
  6. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    A subquery is correlated when it makes use of a value from the parent query in its processing. This means that the subquery will run once for each row returned by the parent query. The following is a correlated subquery that will query the EMPLOYEES table and return the highest earning employee(s) in each department.

    Code (Text):
    SELECT department_id, employee_is, salary
    FROM   hr.employees emp_parent
    WHERE  1 = (SELECT  COUNT(DISTINCT salary)
                FROM   hr.employees emp_subq
                WHERE   emp_parent.department_id = emp_subq.department_id
                AND     emp_parent.salary <=  emp_subq.salary
               );
    Now what Jagadekara said earlier is partially correct (although better than what I said initially because I was ignoreing correlated subqueries entirely). In the above query, for every row of the parent query, it will begin processing the row up to the point where the condition related to a subquery is required. The subquery will then run and use the DEPARTMENT_ID and SALARY values in the current row of the parent query as part of the logic to generate a value. That value is then used by the parent query to complete processing the row and determine if it is returned to the calling process. For each row then, the steps are Parent->Child->Parent.

    This is one of the main reasons correlated subqueries are ugly and resource intensive. Avoid them wherever possible.
     
    Last edited: Jul 20, 2015
    jagadekara, mukulverma2408 and Bharat like this.