I agree with Johannes Krackowizer... the two queries are not the same. The correlated subquery is effectively an outer join. After a bit of repair to make both queries do exactly the same thing and a bit of code to take the display time out of the picture, let's run the code and take some statistics to see what the real story is...

DECLARE @Bitbucket INT,

@BITBUCKET1 [varchar](100)

SET STATISTICS TIME,IO ON

--Test 3

SELECT@Bitbucket = EmployeeID,

--Salary,

--RActive,

@BITBUCKET1 = (SELECT ManagerName

FROMManagers

WHEREManagers.ManagerID = Employees.ManagerID)

FROMEmployees

WHEREEmployees.ManagerID = 1

--Test 4

SELECT@Bitbucket = EmployeeID,

--Salary,

--RActive,

@BITBUCKET1 = ManagerName

FROMEmployees

LEFT JOIN Managers ON (Managers.ManagerID = Employees.ManagerID)

WHEREEmployees.ManagerID = 1

SET STATISTICS TIME,IO OFF

Here are the results... note that the correlated sub-query actually wins in this particular run...

Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Managers'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Employees'. Scan count 1, logical reads 121, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 47 ms, elapsed time = 56 ms.

Table 'Worktable'. Scan count 1, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Managers'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Employees'. Scan count 1, logical reads 121, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:

CPU time = 62 ms, elapsed time = 57 ms.

As you can see... no difference except for minor differences in CPU and Duration. If you run the code several times, you'll find the each takes turns winning the race.

Contrary to what this article states, there is no difference between correlated subqueries that use an equi-join (equals sign) and the equivalent outer join in SQL Server. Even if you make the mistake of using an inner join, the correlated sub-query frequently ties the inner join for CPU and usually beats it for duration. Try it and see.

The moral of the story is DO NOT RELY ON ESTIMATED OR ACTUAL EXECUTION PLAN COSTS OR PERCENT-OF-BATCH TO SELECT THE MOST PERFORMANT QUERY BECAUSE THE EXECUTION PLAN LIES! A sub-lesson includes "make sure both tests are actually the same".

In Oracle? The differences between correlated sub-quireies and outer joins is a totally different story because the optimizer in Oracle is quite different. But one thing remains the same even in Oracle... when you test, you have to test things that work the same and you have to actually test for performance instead of relying on what the execution/explain plan shows.