GeekInterview.com
Series: Subject: Topic:
Question: 235 of 429

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

Asked by: srinuv_11 | Member Since Oct-2006 | Asked on: Nov 7th, 2006

View all questions by srinuv_11   View all answers by srinuv_11

Showing Answers 1 - 6 of 6 Answers
MADHURI

Answered On : 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 QueryEX:    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   

  
Login to rate this answer.

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 ewhere deptno = ( select deptno from emp awhere a.empno = e.empno);2. select e.* from emp ewhere 3 >= ( select count(*) from empwhere sal >= e.sal)order by sal desc

  
Login to rate this answer.
srinivasarao

Answered On : 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

Yes  1 User has rated as useful.
  
Login to rate this answer.
g_sidhu

Answered On : 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.

  
Login to rate this answer.

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

  
Login to rate this answer.

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

  
Login to rate this answer.

Give your answer:

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

Related Open Questions

Ads

Connect

twitter fb Linkedin GPlus RSS

Ads

Interview Question

 Ask Interview Question?

 

Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.