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!

Sub Query Nested Query and Co-Related Subquery

Discussion in 'SQL PL/SQL' started by Julian, Nov 20, 2009.

  1. Julian

    Julian Forum Advisor

    Messages:
    48
    Likes Received:
    1
    Trophy Points:
    90
    Hi friends,

    This is an interview question, I may be asked. What is the difference between Sub Query, Nested Query and Co-Related Subquery in Oracle.

    Thanks for helping me. :)
     
  2. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Dear friends,

    There may exist more than one difference for this topic,just verify one of those difference mentioned here.

    SUBQUERY: query written with in main query.

    e.g:
    Code (SQL):

    SELECT   empname
      FROM   emp
     WHERE   deptno = (SELECT   deptno
                         FROM   dept
                        WHERE   deptname = 'IT')
    Nested query: query written with in sub query

    Code (SQL):
    SELECT   empname
      FROM   emp
     WHERE   deptno = (SELECT   deptno
                         FROM   dept
                        WHERE   deptname = 'IT'
                                AND sal > (  SELECT   AVG (sal)
                                               FROM   dept
                                           GROUP BY   deptno))
    co related subquery: A subquery which reference a colomn already being refered in main query.

    eg:
    Code (SQL):

    SELECT   empname
      FROM   emp
     WHERE   deptno = (SELECT   deptno
                         FROM   dept d, emp e
                        WHERE   deptname = 'IT' AND e.deptno = d.deptno)
     
  3. Julian

    Julian Forum Advisor

    Messages:
    48
    Likes Received:
    1
    Trophy Points:
    90
    Hey Laxman, thanks a lot for the examples! :)
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Nice Demo.. Laxman.....