LEFT JOIN with GROUP BY do not return expected LEFT JOIN result

Question

I'm trying to get all my accumulated sales for each store, even if the value is null (no order for the conditions), but the LEFT JOIN only gives me rows that have a correspondence, which does not suit me :

SELECT s.identifierExt as StoreID, 
YEAR(o.creation) AS Year,
MONTHNAME(o.creation) AS Month,
MONTH(o.creation) AS IDMonth,
ROUND(SUM(o.price), 2) AS Sales
FROM store s
LEFT JOIN order o ON o.store = s.id 
AND (o.creation < '2018-09-13 00:00:00') 
AND (o.place NOT IN ('PENDING','CANCELLED')) 
AND (o.creation > '2018-01-12 00:00:00')
GROUP BY Year, Month, StoreID
ORDER BY IDMonth, StoreID ASC

Thanks in advance.

do you get orders that don't have a row in the "order" table with nulls for the Year and the Month? — Matias Thayer, Apr 16 '19 at 08:28
Nope, what i got is some month/year raws with all the stores, and some other with some store missing (because no order for them). What i want is to have each time the Raw, even with null values — Khalid Ibn Al Walid, Apr 16 '19 at 12:14

score 0 · Answer 1 · answered Apr 16 '19 at 08:10

Possible it's because of the difference between INNER and OUTER joins. The inner join requires that there is a corresponding row (match on the join condition), the outer join does not have such a requirement: it'll return columns with NULL.

It seems you want an OUTER join:

SELECT s.identifierExt as StoreID, 
YEAR(o.creation) AS Year,
MONTHNAME(o.creation) AS Month,
MONTH(o.creation) AS IDMonth,
ROUND(SUM(o.price), 2) AS Sales
FROM store s
LEFT OUTER JOIN order o ON o.store = s.id 
AND (o.creation < '2018-09-13 00:00:00') 
AND (o.place NOT IN ('PENDING','CANCELLED')) 
AND (o.creation > '2018-01-12 00:00:00')
GROUP BY Year, Month, StoreID
ORDER BY IDMonth, StoreID ASC

This way you'll also get stores without orders.

Also see this answer on the difference between inner and outer joins

"LEFT JOIN" and "LEFT OUTER JOIN" are the same thing. The word "outer" is optional — Matias Thayer, Apr 16 '19 at 08:31
You're right, it defaults to the outer join. Bit of a slip... So you're already doing an outer join. Ignore this answer. — Rubenz, Apr 16 '19 at 09:04

LEFT JOIN with GROUP BY do not return expected LEFT JOIN result

1 Answers1