What is correlated sub-query

Editorial / Best Answer

rampratap409  

  • Member Since Sep-2006 | Dec 7th, 2006


when a sub query executes for each row its called a corelated sub query, in other words when a sub query takes the input from outer query for executation then execute and passes the result to outer query then outer query executes.

Showing Answers 1 - 7 of 7 Answers

Aalishan Moosavi

  • Jun 30th, 2005
 

A correlated sub-query is a subquery that references the value/s from the main query. 
example :  
SELECT colA 
, colB  
FROM tableA 
WHERE colB < ( SELECT max(colX)  
FROM tableB 
WHERE tableB.colY = tableA.colA) 
Oracle executes correlated subquery for each record from of main query .. hence these types of queries have greater impact on performance and must be avoided

In correlated subqueries, the information from the outer select statement participates as a condition in the inner select statement.

Eg :  Select deptno, ename, sal

    from emp a 

where sal> (select avg(sal) from emp b where a.deptno =b.deptno)

order by deptno;

  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.

 

Related Answered Questions

 

Related Open Questions