This months T-SQL Tuesday blog party is being hosted by Sebastian Meine, PhD (blog). The topic is on JOINS. I’ve chosen to blog about how to rewrite a RIGHT JOIN I found this week in a stored procedure.The query was simple, but it was difficult to read without creating a data model and understanding the relationships of the tables. I decided to rewrite it to make it easier to maintain in the future. I have created a sample data model to demonstrate the problem and the solution. The data model has a table for Sales Reps,a table for Regions which the Sales Reps belong to (optionally), a list of Clients, and a linking table which links the Clients and the Sales Reps.
The query that was created, returns all the Clients and any associated Sales Reps with their regions, even if the Sales Rep is not assigned to a region. The original programmer used a RIGHT JOIN to join the SalesRep table to the ClientSalesRep table. They also put the predicate for the SalesRegion table after the SalesRep table. While the Optimizer has no problem reading this query, I had to stand on my head to figure it out.
SELECT c.ClientID ,c.ClientName ,sr.Region ,srep.FirstName ,srep.LastName FROM dbo.Client AS c LEFT JOIN dbo.ClientSalesRep AS cus ON c.ClientID = cus.ClientID LEFT JOIN dbo.SalesRegion AS sr RIGHT JOIN dbo.SalesRep AS srep ON srep.SalesRegionID = sr.SalesRegionID ON cus.SalesRepID = srep.SalesRepID GO
I rewrote the query using only LEFT JOINS and each table had its own predicate. I found the LEFT JOINS made it easier to read and didn’t give me a headache.
SELECT c.ClientID ,c.ClientName ,sr.Region ,srep.FirstName ,srep.LastName FROM dbo.Client AS c LEFT JOIN dbo.ClientSalesRep AS cus ON c.ClientID = cus.ClientID LEFT JOIN dbo.SalesRep AS srep ON cus.SalesRepID = srep.SalesRepID LEFT JOIN dbo.SalesRegion AS sr ON srep.SalesRegionID = sr.SalesRegionID GO
I populated the tables with a million rows to see if the Optimizer would treat these queries differently. It didn’t. They had the same query plan, the same number of reads, and the same statistics, but it was easier to read.
Thanks go to Sebastian for hosting T-SQL Tuesday this month. Check out Sebastian’s blog, because he is blogging about JOINS all month.