I can honestly say that I have never seen subqueries used as such, these queries have always been of the inner join variety. And judging by the discussion from months ago, this will be something I remember in the future.

Anyways, I have seen a variation of the "standard" inner join

SELECT EmployeeID, Salary, RActive, ManagerName

FROM Employees

INNER JOIN Managers

ON Manangers.ManagersID = Employees.ManagerID

WHERE Employees.ManagerID = 3

In which the WHERE is eliminated and is replaced with using an AND on the JOIN portion

SELECT EmployeeID, Salary, RActive, ManagerName

FROM Employees

INNER JOIN Managers

ON Manangers.ManagersID = Employees.ManagerID

AND Managers.ManagerID = 3

On larger datasets I have seen better performance with the second version, but on smaller I see no performance advantage.

I did do a test on a Products:Categories relationship on one of the DBs I have mounted, where there most populated category had about 50 items. I used the subset and the 2 variations of the join on both an '08 box and a 2K instance.

The results were fairly equal, with the subset at 34% and the joins at 33% apiece. This was consistent between both server instances