Left join that is not showing all left table values

Question

A LEFT JOIN should show all rows on the left table however when running the following query, I am only getting place values where there is a count of 1 or more. Please can someone give me some guidance as to where I might be going wrong:

SELECT places.placeId,
       placeName,
       COUNT(orderId) AS orderCount
FROM   places
       LEFT JOIN orders
         ON places.placeId = orders.placeId
WHERE  places.companyId = 1
       AND stateId = 1
       AND orderstateId = 1
       AND orderName NOT LIKE 'Delivery%'
GROUP  BY places.placeId,
          places.placeName
ORDER  BY orderCount DESC,
          placeName ASC 

Thanks in advance

Solution 1

Your WHERE condition converts the OUTER JOIN back to an INNER JOIN.

The non matched rows will have NULL for all the orders columns and be eliminated by the WHERE clause. Try this.

SELECT places.placeId,
       placeName,
       COUNT(orderId) AS orderCount
FROM   places
       LEFT JOIN orders
         ON places.placeId = orders.placeId
       AND orders.stateId = 1
       AND orders.orderstateId = 1
       AND orders.orderName NOT LIKE 'Delivery%'
WHERE  places.companyId = 1
GROUP  BY places.placeId,
          places.placeName
ORDER  BY orderCount DESC,
          placeName ASC 

Solution 2

If these columns stateID, orderstateID, and OrderName came from ORDERS table then you will have a problem then. As you can see, If places.placeID is not present on orders table, the following columns are nulls for the orders table. And that's the reason why some records won't show because of the condition you have provided on orders table.

Solution 3

Even when you use a LEFT JOIN, all rows have to meet the conditions in the WHERE clause. In this case, the WHERE clause has requirements related to orders, thus you won't return any rows where orders doesn't match to places.

A simple fix would be to allow null values to satisfy your WHERE clause:

orderstateId = 1

changes to

(orderstateId is NULL or orderstateId = 1)

And likewise for the other variables.

This would probably do what you want, but you have to check if rows with null values in the orders table could then be included and mess up your result. It might be better to redesign your query in another way.