SQL Kiwi (12/6/2011)
Hi Hugo,Writing a question to demonstrate the equivalence of SQL expressions is very brave, so well done for that. Small point: in the explanation you say, "the only way to rewrite the query is to reverse the table order", but this code expresses the same SQL semantic I think:
SELECT
e.Name,
c.Name,
o.OrderDate
FROM dbo.Employees AS e
LEFT JOIN
(
dbo.Orders AS o
JOIN dbo.Customers AS c ON
c.CustomerID = o.CustomerID
) ON
e.EmployeeID = o.CustomerID
AND o.OrderDate > DATEADD(MONTH, -4, CURRENT_TIMESTAMP)
WHERE
e.Position = 'SalesRep'
Personally I find this layout easier to read than 'nested' ON clauses.
Very nice format to visually show the "nestedness" of the joins.
One minor flaw I'd attribute to speedily writing this query--the above one will only yield the same result as the original one if only employees are the customers and the IDs as customer and employee are equal.
Changing the bolded ON clause to
e.EmployeeID = c.SalesRep
will correct the issue and return the same result (and execution plan) as the original one (to verify that I used the sample data Hugo provided).
A great big thank you again to Hugo for the question and to Paul for the appealing reformatting of the query.
-Michael