>>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