Subqueries
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Subqueries

  1. #1
    Join Date
    Nov 2000
    Posts
    416

    Question

    Can you explain to me in a plain english with basic example what is the difference between reqular subqueries and correlated subquries ? It's a little fuzzy to me.

    An ounce of prevention is worth a pound of cure

  2. #2
    Join Date
    Jun 2001
    Location
    Belgium
    Posts
    13
    Hi ,
    from the oracle documentation ...

    Oracle performs a correlated subquery when the subquery references a column from a table referred to in the parent statement

    The following statement returns data about employees whose salaries exceed their department average. The following statement assigns an alias to emp, the table containing the salary information, and then uses the alias in a correlated subquery:

    SELECT deptno, ename, sal
    FROM emp x
    WHERE sal > (SELECT AVG(sal)
    FROM emp
    WHERE x.deptno = deptno)
    ORDER BY deptno;

    hope this helps,

    greetings,

    Marc

  3. #3
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Solution

    Hi, 7th june 2001 10:19 hrs chennai

    SubQuery:
    ------------

    In normal condition the sub query Evaluates first with a Output value to the main Query based on which the main Query is executed ONCE.

    correlated subQuery
    -----------------------

    In this type of subquery for each and every record in the main query the sub query is invoked

    Follow the example as above here what the emp table does is for every record in the main Query table (of the same emp table ) in the inner sub queries evaluates average sal is calculated for the partcular deptno(as per join condition and the i.e on the main query Deptno and inner query deptno) and compared for the condition to pass.

    cheers

    padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width