Correlated sub-query vs. sub-query

Outer query will be: Select CustomerID, SalesOrderID,OrderDate FROM Sales.SalesOrderHeader

1. Outer query provides a CustomerID to inner query.



2. Inner query retrieves the MININUM OrderDate for the CustomerID.



3. Outer query uses the MINIMUM OrderDate to retrieve the record for that CustomerID.
   

That above process will repeat for each CustomerID.

Sub-query: inner query (red color) will not reference the data from outer query.

SELECT * FROM HumanResources.Employee
WHERE EmployeeID IN
(SELECT DISTINCT EmployeeID FROM HumanResources.JobCandidate)

Both outer query and inner query are executed one time.