T-SQL Tuesday #37 – RIGHT JOIN, LEFT JOIN, raw, raw, raw

T-sql TuesdayThis 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.

Comments are closed.

%d bloggers like this: