SQL Server- Difference between Self contained & Co-related subquery

The Sub Query which is independent of the outer query is known as Self contained query.

The Sub query which has reference to the columns of the tables from the outer query.

 

Seld Contained subquery are good in a sense that debugging is easy, but there might be certain business scenario where we might have to used Co-related sub query

 

Example

Self Contained sub query

SELECT EmpName FROM Emp WHERE EmpId in (                      SELECT EmpId                      FROM Sales                      WHERE SalesAmount > 5000                      )

 

Co-related Sub query

SELECT CustId,EmpId FROM Orders o1 WHERE OrderDate  IN (                            SELECT MAX(OrderDate)                            FROM Order o2                            WHERE o1.CustId = o2.CustId                            )