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

What table are all the various columns in your query from? If any of `stateId,orderstateId,orderName` are in `orders` you need to move the relevant filter into the join rather than the `where`. — Martin Smith, Oct 01 '12 at 14:25
stateId, orderstateId and orderName are all from the orders table. I can get round it by doing a second query that gets all places which have no orders but I was trying to avoid that if possible — Michael Wilson, Oct 01 '12 at 14:40
@Barmar - Done. Was waiting for the OP to confirm the columns rather than supplying an answer based on possibly wrong assumptions. — Martin Smith, Oct 01 '12 at 14:45
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Jan 05 '22 at 07:43

score 33 · Accepted Answer · answered Oct 01 '12 at 14:41

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

score 1 · Answer 2 · answered Oct 01 '12 at 14:28

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.

score 1 · Answer 3 · answered Oct 01 '12 at 14:35

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.

score 0 · Answer 4 · answered Oct 01 '12 at 14:27

0

You are saying orderstateId = 1. I guess this binds to orders.orderstateId = 1. This conditions fails for a null orderstateId which happens when left-joining without match.

Change it to orders.orderstateId = 1 OR orders.orderstateId IS NULL

answered Oct 01 '12 at 14:27

usr

168,620
35
240
369

Left join that is not showing all left table values

4 Answers4