>>>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
>
>Thank you for helping me understand the SQL syntax, I also follow your comments on SQL Server so thanks for posting those as well.
>Here is the query, it joins Employees with EmpItems one time to get info from itCode = 10 and a second to get info from itCode = 805. The first join is the way how I usually would have done it and the second one the derived table subquery.
>
>

>SELECT Employees.emNumber,
>		Employees.emPersNr,
>		Employees.emStartWrk,
>		Employees.emEndWrk,
>		Employees.emName,
>		Employees.emFirst,
>		Employees.emBirthDat,
>		EmpItems.itSwitch1 AS WeekDays,
>		CAST(EmpItems.itFreq AS Character(15)) AS PayFrequency,
>		EmpItems.itDefHrs AS DefaultHours,
>		EmpItems.itBaseAmt AS BaseAmount,
>		EmpItems.itBasePHr AS HourWage,
>		Item805.SpecialPerc AS SpecialPerc,
>		CAST(EmpItems.itSalaried AS Character(15)) AS PaidBy,
>		Departments.deCode,
>		Departments.deName,
>		Employees.emIdNr,
>		Employees.emKey,
>		PaySet.paKey
>	FROM Employees
>	INNER JOIN Departments
>	ON Employees.emDeKey = Departments.DeKey
>	INNER JOIN PaySet
>	ON Employees.emKey = PaySet.paEmKey
>	LEFT OUTER JOIN Functions
>	ON Employees.emFuKey = Functions.fuKey
>	LEFT OUTER JOIN StatusEmp
>	ON Employees.emStKey = StatusEmp.stKey
>	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
>

There is no need to use derived table in the second join also. Just give different alias of EmpItems:

...
LEFT JOIN EmpItems
     ON Employees.emKey = EmpItems.itEmKey AND EmpItems.itCode = 10
LEFT JOIN EmpItems Item805
     ON Employees.emKey = Item805.itEmKey AND EmpItems.itCode = 805
..

You should use that alias everywhere in the query.