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

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

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

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.

The post Broken Left Join appeared first on The Database Avenger.