To me it looks like someone was looking to add the code persontype = 'sp', but just put it in the wrong place. I've seen code like that before, when you tell a new person to just add that code to the join and they put it on the wrong one. Doesn't fail, but sure was slower.

My thoughts that it should be more written like one of these, depending on the results needed as other have stated.

SELECT TOP 1000 p.BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion,

AdditionalContactInfo,

st.ModifiedDate

FROM AdventureWorks2008R2.Person.Person p

JOIN Person.PersonPhone pp ON

p.BusinessEntityID = pp.BusinessEntityID

AND p.persontype = 'sp' -- moved to the correct join

LEFT JOIN Sales.SalesTerritoryHistory st ON

pp.BusinessEntityID = st.BusinessEntityID

AND st.modifieddate BETWEEN '2005-06-24 00:00:00.000' AND '2006-06-24 00:00:00.000'

ORDER BY modifieddate DESC

-- OR

SELECT TOP 1000 p.BusinessEntityID,

PersonType,

NameStyle,

Title,

FirstName,

MiddleName,

LastName,

Suffix,

EmailPromotion,

AdditionalContactInfo,

st.ModifiedDate

FROM AdventureWorks2008R2.Person.Person p

JOIN Person.PersonPhone pp ON

p.BusinessEntityID = pp.BusinessEntityID

LEFT JOIN Sales.SalesTerritoryHistory st ON

pp.BusinessEntityID = st.BusinessEntityID

AND st.modifieddate BETWEEN '2005-06-24 00:00:00.000' AND '2006-06-24 00:00:00.000'

WHERE p.persontype = 'sp' -- or moved to the WHERE

ORDER BY modifieddate DESC