honza.mf (12/6/2011)
Hi!I don't understand how the condition
AND o.OrderDate > DATEADD(month, -4, CURRENT_TIMESTAMP)in SQL Query #4 works. For me it seems it is to eliminate the employees with no appropriate order attached.Have I overlooked something?
To have 100% same behaviour, I use:
SELECT e.Name AS SalesRep, c.Name AS Customer, o.OrderDate
FROM dbo.Customers AS c
INNER JOIN dbo.Orders AS o
ON c.CustomerID = o.CustomerID
AND o.OrderDate > DATEADD(month, -4, CURRENT_TIMESTAMP)
RIGHT JOIN dbo.Employees AS e
ON e.EmployeeID = c.SalesRep
WHERE e.Position = 'SalesRep';
Hi Honza.mf,
In query #4, the first step is the inner join between orders and customers. So for the right outer join, the left-hand side is a row set consisting of orders with their customers (where both orders without customer and customers without orders are already filtered out). On the right-hand side is the unfiltered table of employees.
The join will attempt to match each row on the left-hand side with each row on the right-hand side. It will retain combinations where the condition is true (the employee on the right-hand side is the sales rep for the order, and the order is no older than four months), and it will also retain rows from the right-hand side (employees) where the condition is not true for any of the rows of the left-hand side.
Your query filters out old orders before the outer joins even starts. I compared it to the #4 query, by logical comparison, running against my test data, and comparing execution plans. I did not find any differences, so it is also a correct rewrite of the original query - and one that is easier to comprehend! Kudos!
That being said, I have run into sitiuations where I needed to choose between the join pattern of the original query (with the nested joins), or that of query #4 (with the weird placement of the ON condition). I am 99% sure that a rewrite such as yours was not possible in that case. My goal was to reconstruct such a case for this question, and I obviously did not completely succeed at that. Fortunately, this does not invalidate the QotD (as the question is to identify the query that is exactly equivalent, not to produce the best rewrite).