SQL Server - Corelated and subquery in sql server

Asked By shoaib mansoori on 27-Jun-12 03:06 AM

Hi all,
what is difference between corelated and subquery in sqlserver.
is corleated subquery and non corelated subquery exist in sql server if yes the difference of this and where we should use one of them.
explain the concept with simple illustration if you can.
thanks

Jitendra Faye replied to shoaib mansoori on 27-Jun-12 03:13 AM

Correlated Subquery:-If the outcome of a subquery is depends on the value of a column of its parent query table then the Sub query is called Correlated Subquery.

Subquery:-If a sql statement contains another sql statement then the sql statement which is inside another sql statement is called Subquery. It is also known as nested query. The Sql Statement which contains the other sql statement is called Parent Statement.

Refer this link-

http://vivekjohari.blogspot.in/2009/09/difference-between-subquery-nested.html

Here you will get example also.

Ravi Maurya replied to shoaib mansoori on 27-Jun-12 03:15 AM

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

S K replied to shoaib mansoori on 27-Jun-12 03:16 AM

Actually SubQuery can be Correlated or Noncorrelated.
A correlated subquery is an inner subquery which is referenced by the main outer query such that the inner query is considered as being executed repeatedly.
eg
SELECT e.EmpID FROM Employee e WHERE e.ContactID IN
(
SELECT c.ContactID FROM EmpDetails c WHERE MONTH(c.ModifiedDate) = MONTH(e.ModifiedDate)
)

and A noncorrelated subquery is subquery that is independent of the outer query and it can executed on its own without relying on main outer query .
eg
SELECT e.EmpID FROM Employee e WHERE e.ContactID IN
(
SELECT c.ContactID FROM EmpDetails c WHERE MONTH(c.Dept = 'IT')
)

hope this helps you

Lalit M replied to shoaib mansoori on 27-Jun-12 04:22 AM

Subquery :-This is a query which is used as part of a larger query. Much as you would select from a table, you can select from a sub-query.
Correlated subquery :- this is a particular type of sub-query that references the outer query.

Read more:-http://www.sqlservercentral.com/Forums/Topic848551-360-1.aspx#bm848734

Chintan Vaghela replied to shoaib mansoori on 27-Jun-12 04:42 AM

Hi Frndz,

Functionality: Corelated Query and Sub Query

CoRelated Query

A correlated subquery is a subquery that is evaluated once for each row of the outer query.

Correlated query excuted once the row of the outer query.

Corelatd query is more faster then join.

Diffrence Between Corelated sub Query and sub Query

Corelated subquery make refrence using correlation variable, to the outer query

Folloiwng example include correlated and simple subquery.

select studentname ,

( select avg(studentmark) from students where class = t1.class ) as classaverage ,

( select avg(studentmark) from students ) as schoolaverage from students t1

When return classaverage then this corelated becasue t1 is corelation vaiable refrence using in sub query

When schoolaverage then there is no any refrence in sub query

Hope this helpful!

Thanks