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

 

 

 

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Dec 4th, 2006

 

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

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

 Profile 

• 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.

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Feb 23rd, 2010

 

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);

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Feb 23rd, 2010

 

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);

upendra

• Jun 11th, 2015

 

Sub Query: Inner query is executed first then it gives to outer query and outer query is executed.

Correlated Query: Outer query executed first row by row then it based on inner query is executed.