• 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.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/