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.

Query:


SELECT *,  ( SELECT Sum(m.Name1)
  FROM MATHTBL M
  WHERE m.id <= m2.id
  ) AS Result
FROM MATHTBL m2

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.

Example:

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