What is difference between sub query and correlated sub query, Give the example for this.

Questions by srinuv_11   answers by srinuv_11

Showing Answers 1 - 6 of 6 Answers

MADHURI

  • Nov 28th, 2006
 

CORRELATED SUBQUERIES:   Is evaluated for each row processed by the Main query. Execute the Inner query based on the value fetched by the Outer query. Continues till all the values returned by the main query are matched. The INNER Query is driven by the OUTER Query

EX:    SELECT empno,fname,sal,deptid FROM emp e WHERE sal=(SELECT AVG(sal) FROM emp WHERE deptid=e.deptid)

The Correlated subquery specifically computes the avg(sal) for each department.

SUBQUERY:      Runs first,executed once,returns values to be used by the MAIN Query. The OUTER  Query is driven by the INNER QUERY

 

 

 

  Was this answer useful?  Yes

subquery:  ( subquery executes only once and gives the output ot outer query then outer query executes)

select * from emp where deptno in ( select deptno from dept);

co-related query : ( co-related subquery executes for each row returned by main query )

select a.* from emp e

where deptno = ( select deptno from emp a

where a.empno = e.empno);

2. select e.* from emp e
where 3 >= ( select count(*) from emp
where sal >= e.sal)
order by sal desc

  Was this answer useful?  Yes

srinivasarao

  • Feb 7th, 2007
 

Hi

Corelated sub query will fire for each row and sub query will fire only once like that easy to remember

Srinivasa Rao K

g_sidhu

  • Feb 12th, 2008
 

With a normal nested subquery, the inner SELECT query runs first and executes once, returning values to be used by the main query. A correlated subquery, however, executes once for each candidate row considered by the outer query. In other words, the inner query is driven by the outer query.

  Was this answer useful?  Yes

subquery :
                     returns value to main query. it wont get values from main query.
Example:

 SELECT id "Emp #", First_name "Name", salary "Salary", ROWNUM rank 
FROM 
(SELECT id, first_name, salary FROM employee ORDER BY salary);

corelated subquery :
                           get value from main query and does validation then returns to main query. its used to do row by row validation. performance is slow.
Example:
SQL> SELECT empno, mgr,ename, sal FROM emp outer WHERE sal > (SELECT AVG(sal) FROM emp inner WHERE inner.empno = outer.mgr);

  Was this answer useful?  Yes

subquery : returns value to main query. it wont get values from main query.

Example:
SELECT id "Emp #" First_name "Name" salary "Salary" ROWNUM rank FROM (SELECT id first_name salary FROM employee ORDER BY salary);
 
corelated subquery : get value from main query and does validation then returns to main query. its used to do row by row validation. performance is slow.

Example:
 
SQL> SELECT empno mgr ename sal FROM emp outer WHERE sal > (SELECT AVG(sal) FROM emp inner WHERE inner.empno outer.mgr);

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account


 
Send   Reset

 

Related Answered Questions

 

Related Open Questions