Today I came to know across a feature of SQL Server known as correlated query and noncorrelated query.Then the certain question comes in my mind:

What is difference between corelated and subquery in SQL Server? 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.

I would like to know it.

2

1 Answer

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.

Example:

USE DatabaseName; GO SELECT e.EmpID FROM HumanResources.Emp e WHERE e.ContactID IN ( SELECT c.ContactID FROM Person.Contact c WHERE MONTH(c.ModifiedDate) = MONTH(e.ModifiedDate) ) GO

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.

Example:

USE DatabaseName; GO SELECT e.EmpID FROM HumanResources.Emp e WHERE e.ContactID IN ( SELECT c.ContactID FROM Person.Contact c WHERE c.Title = 'Mr.' ) GO

More at the the link.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password

Post as a guest

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.