honza.mf (12/6/2011)
Probably I will use right joins and nested joins only in case of self-defense. I prefer the readability and I think from left to right.
Absolutely true. This question was intended to illustrate how a real-life problem sometimes forces you to choose the lesser of two evils. Both nested joins and right outer joins reduce readability and ease of understanding of a query - so pick your poison. I have been in this position a few times, and whatever I chose, I always felt it was not the optimal choice.
BTW, now that I no longer have to support SQL Server 2000, I can use a better solution using a Common Table Expression:
WITH CustomersWithRecentOrders AS
(SELECT c.Name, o.OrderDate, c.SalesRep
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, co.Name AS Customer, co.OrderDate
FROM dbo.Employees AS e
LEFT JOIN CustomersWithRecentOrders AS co
ON co.SalesRep = e.EmployeeID
WHERE e.Position = 'SalesRep';
Note that this query does NOT produce the exact same execution plan as the original, but it DOES produce the exact same results. And of all the options, I think this one is the most readable.