Follow by Email

Monday, May 27, 2013

SQL Server correlated and uncorrelated Subqueries Example and Explanation | Difference between correlated and uncorrelated Subqueries with Example

1. Correlated Sub Query :

Correlated sub query are those which work on row by row basis. As for every row it takes the values from the main Query into the sub-query to return a value. Thus it is dependent on the main query i.e Outer Query.

Suppose we have a table like this below :

And we want the output as 

To get the result column in the above image we will make use of a correlated query to get the desired output.


SELECT *,  ( SELECT Sum(m.Name1)
  WHERE <=
  ) AS Result

The Inner Query is called as Correlated Query.

2. Uncorrelated Subquery :

The uncorrelated query is not dependent on the main query. It Just provides the values
 required to the main query.


SELECT empname
FROM Employee
WHERE empid IN (
  SELECT empid
  FROM empsalary
  WHERE empsal > 1000