>I wonder if the join using the subquery would mean the query first selects all records that match code 805 and then does the join, or it would select only those from EmpItems that actually do match the where clause?
>
>Let's say the EmpItems table has a lot of records, would the first join be substantially faster than the second?
>
>

>...
>LEFT OUTER JOIN EmpItems
>ON Employees.emKey = EmpItems.itEmKey AND EmpItems.itCode = 10
>LEFT OUTER JOIN 
>	(SELECT EmpItems.itEmKey AS itEmKey,
>	EmpItems.itPerc AS SpecialPerc
>	FROM EmpItems
>	WHERE EmpItems.itCode = 805)
>	AS Item805
>ON Employees.emKey = Item805.itEmKey
>

Christian, a couple of things.

First, the subquery on the inside (in parenthesis) is what's called a derived table subquery. It's a subquery that could stand on its own. SQL Server will take the result of (select from empitems where itcode = 805) and essentially treat it as a table (with the alias of Item805), because you're doing just that....created a derived table through a subquery. It will then join that derived table subquery to whatever you join on the outside.

Before answering the result of your question, would you be able to post the entire query? (assuming it's not so complicated that it would take a long time to understand). I wanted to give a 2nd answer, but would like to see the rest of the query.

Kevin