What is CoRelated SubQuery

A subquery within a subquery

Questions by vardhan_81   answers by vardhan_81

Showing Answers 1 - 15 of 15 Answers

sukumar_viswa

  • Nov 24th, 2005
 


It is very similar to sub-queries where the parent query is executed based on the values returned by sub-quries. but when come?s to co-related subqueries for every instance of parent query subquery is executed and based on the result of sub-query the parent query will display the record as we will have refernce of parent quries in su-queries we call these as corelated subquries.
so, we can define co-related sub query as for every record retrival from the sub query is processed and based on result of process the parent record is displayed.

vinod

  • Jun 22nd, 2006
 

In this query main query will executes first. sub query will depends on the main query. It fallows top down approach.

In normal sub queries sub query will executes first. It fallows down to top approach.

Renuka Rajput

  • Jul 29th, 2006
 

The main differencre between subquery and co-related subquery is that in subquery child query is executed first n then parent,but in co-related subquery main query is executed first(even though parenthesis are present) and then child query.

Example of co-related subquery

select dname from dept where exists
(select deptno from emp
where dept.deptno=emp.deptno);

select dname from dept where not exists
(select deptno from emp
where dept.deptno=emp.deptno);

  Was this answer useful?  Yes

madhug56

  • Jun 5th, 2007
 

A correlated subquery is a subquery that contains reference to a table that also appears in the outer query.

SELECT * FROM t1 WHERE column1 = ANY
       (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

  Was this answer useful?  Yes

rajakumar_na

  • Nov 4th, 2007
 

A subquery which execute once each and every row of a main query is called correlated subquery. it is mainly used for row by row process

SELECT * FROM t1 WHERE column1 = ANY
       (SELECT column1 FROM t2 WHERE t2.column2 = t1.column2);

  Was this answer useful?  Yes

sirisha.hari

  • Jan 6th, 2008
 

hi
eg is to get the manager of the employe in emp table by using  eno,mgr coloumn names, where eno contains emp ids and mgr contains emp id of manager.

the following query is


select e.ename,ename manager  from emp e where e.mgr=(select eno from emp

                    where e.mgr=eno)

  Was this answer useful?  Yes

jagadeesh9

  • Feb 22nd, 2008
 

It will first execute the outer query for each is going to execute the all the rows in inner query.


example:
           
Select * from emp a where &n=(select count(distinct(b.sal)) from emp b where

                                                b.sal<=a.sal);

  Was this answer useful?  Yes

sachin_dac

  • May 22nd, 2008
 

Subquesries are of 4 types -
Single row subquery
Multiple row subquery
Inline Views and
Multiple Column Subquery

In case of Multiple row subquery, we can use any operator other than equals to(=) or simply use IN operator to make the Parent Subquery able to handle these Multiple returning rows.

There is one more type of Multiple Row Sunquery and that is Corelated Sub Query.


The Corelated Sunqueries are those SubQueries in which we simply give the reference in Child Subquery of any coloumn of parent Subquery. For writing Corelated Subqueries SQL Provides us a new operator EXISTS.


Return type of Exists is Boolean It returns TRUE if Child SubQuery returns any row otherwise Exists returns FALSE. So while writing COrelated Subqueries make sure that the Child Subquery returns atleast one single row.


There is one more difference between subqueries and Corelated Subqueries. And that is related to the Control execution. In case of the
Subqueries first the Child subquery executes and gives the FILTER crieteria to the Parent Subquery. So after completion of the Child Subquery the Parent Subquery got executed. While In case of the Corelated Subqueries First the Parent Subquery executes and then the Child Subquery executes once for every row returned by the Parent Subquery. So we can say that First the Parent Subquery executes and then the Child Subquery.


I can show you a simple example of Corelated Subqueries also:

Select emp.ename, emp.Job, emp.Sal
From emp
Where EXISTS ( select dept.deptno
From dept
Where dept.deptno = emp.deptno
And dept.LOC='NEW YORK');


In this above shown example I am just giving a reference to the deptno column of the EMP Table.


You can just fire this query and see the results. In this subquery I am using the inbuild tables of SQL so you can directly see the results. If I remember correct then this would give 3 Records. (I could also be worng).

  Was this answer useful?  Yes

dashing999

  • Jun 16th, 2008
 

In a subquery
      Outer Query condition is used in the the inner query.

Whereas in a Corelated subquery
      Inner query condition is used in the outer query..

example

select * from emp x where sal>(select avg(sal) from emp where x.deptno=deptno);

  Was this answer useful?  Yes

sandeepkbehera

  • Sep 18th, 2008
 

Oracle performs a correlated subquery when the subquery referances a column from a table referred to in the parrent statement. A correlated subquery is evaluted once each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement .    

  Was this answer useful?  Yes

rjk1203

  • Sep 19th, 2008
 

A Correlated Subquery which executes with value returned by the child Query. Unlike subquery it executes per result from child query.

Ex: removing duplicate rows from a table


Subquery executes once for the entire child query's result

  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