Broken Left Join

Here is a simple example of a LEFT JOIN that returns the sales stats for all employees under 21.

SELECT e.Name, COUNT(s.SaleId) AS Sales, MAX(s.DateOfSale) AS LatestSale

FROM Employees e
LEFT JOIN Sales s ON e.EmployeeId = s.EmployeeId

WHERE e.Age < 21

GROUP BY e.Name

LEFT JOIN with COUNT and MAX of employee sales 1

This will return all employees under the age of 21 even if they haven’t been ruthless enough to make any sales. The employees without a sale will display NULL in the Sales column, and the LatestSale column, as there weren’t any rows to join to in the Sales table for those employees.

Here is an example of a broken LEFT JOIN. The query has been altered to only count sales for the month of March. It does do that BUT it only returns employees who made a sale in March. The LEFT JOIN is no longer working. We want it to return all employees.

SELECT e.Name, COUNT(s.SaleId) AS Sales, MAX(s.DateOfSale) AS LatestSale

FROM Employees e
LEFT JOIN Sales s ON e.EmployeeId = s.EmployeeId

WHERE e.Age < 21
AND s.DateOfSale >= CONVERT(DATE, ‘2016-03-01’)
AND s.DateOfSale < CONVERT(DATE, ‘2016-04-01’)

GROUP BY e.Name

Broken LEFT JOIN with COUNT and MAX of employee sales with filter

This is caused by this part of the filter:

AND s.DateOfSale >= CONVERT(DATE, ‘2016-03-01’)
AND s.DateOfSale < CONVERT(DATE, ‘2016-04-01’)

We have said that a NULL value for s.DateOfSale is not in the range we are interested in. This means the rows with NULLs in the s.DateOfSale column (our employees yet to make a sale) will be filtered out. It will also filter out employees with sales in months other than March. We have converted the LEFT JOIN into an INNER JOIN.

To fix this we can move the filter to the LEFT JOIN. By doing this, we are saying return all employees (including the 0 sales slackers) and only include sales that happened in March.

SELECT e.Name, COUNT(s.SaleId) AS Sales, MAX(s.DateOfSale) AS LatestSale

FROM Employees e
LEFT JOIN Sales s ON e.EmployeeId = s.EmployeeId
                  AND s.DateOfSale >= CONVERT(DATE, ‘2016-03-01’)
                  AND s.DateOfSale < CONVERT(DATE, ‘2016-04-01’)

WHERE e.Age < 21

GROUP BY e.Name

LEFT JOIN with COUNT and MAX of employee sales complete with NULLs

We can now see the performance of each employee in March.

SQLNewBlogger

This was a very quick post based on a simple problem that I see way too often. When reviewing code, I always check the WHERE clause of queries using LEFT JOINs. Try blogging about common issues you see in the field.

About the author

James Anderson

I'm a freelance SQL Server DBA in Southampton, UK. Performance tuning code and improving development processes are what I love to do for work. Outside of work, I live to travel to as many places as possible with my wife Sarah.

2 thoughts on “Broken Left Join”

  1. I think it’s also worth updating the alias to highlight the fact the join is filtered. In this case, something like ‘s_mar2016’ would do. This will save confusion when you revisit the query in six months time!

Leave a Reply

Your email address will not be published. Required fields are marked *

Sign Up

James Anderson SQL Server DBA

Hi, I'm James Anderson. When I'm not blogging about SQL Server, I do this.

The SQL People

DBA Stackexchange Profile