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:
Would this not work as well?
;WITH cte_Subquery AS
(SELECT c.SalesRep, c.Name, o.OrderDate
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
ON o.CustomerID = c.CustomerID
AND o.OrderDate > DATEADD(month, -4, CURRENT_TIMESTAMP))
SELECT e.Name AS SalesRep, s.Name AS Customer, s.OrderDate
FROM dbo.Employees AS e
LEFT JOIN cte_Subquery AS s
ON e.EmployeeID = s.SalesRep
WHERE e.Position = 'SalesRep';
I always go with CTEs in this situation when possible, but I would love to know if I'm off the mark on this one.
EDIT: Of course I posted this 45 seconds after Hugo beat me to it. At least I know I was thinking along the right lines.
ron
-----
a haiku...
NULL is not zero
NULL is not an empty string
NULL is the unknown