How do I do a LEFT JOIN in MySQL, where I have missing key values in the RIGHT table?

Question

I have table B that looks like the following:

DATE       | STORE | PRODUCT | UNITS_SOLD |  
-------------------------------------------
2020-01-01 |     1 | Beans   | 10         |
2020-01-03 |     1 | Beans   | 5          |
2020-01-01 |     2 | Carrots | 6          |
...        |   ... | ...     | ...        |

The important point being that there is not a row present for every date.

I'd like to essentially 'fill in' the missing dates and have null values in the other columns. I've been trying to do this by left joining with a table A that is just a list of continuous calendar dates, i.e.

DATE       |
------------
2019-01-01 |
2019-01-02 |
2019-01-03 |
...

The SQL I have written for this is (and I tried to keep it simple by limiting to one store and one product):

SELECT 
    A.DATE
  , B.STORE
  , B.PRODUCT
  , B.UNITS_SOLD
FROM
    B AS SALES
LEFT JOIN
    A AS DATES ON
      DATES.DATE = SALES.DATE
WHERE
        B.STORE = 1
    AND B.PRODUCT = 'Beans'

But it's just returning rows where the date is present in B. I've tried moving the conditions in the WHERE clause to inside the join too, and adding '...OR IS NULL'.

What I'd like is:

DATE       | STORE | PRODUCT | UNITS_SOLD |  
-------------------------------------------
2020-01-01 |     1 | Beans   | 10         |
2020-01-02 |  null | null    | null       |
2020-01-03 |     1 | Beans   | 5          |
...        |   ... | ...     | ...        |

What am I missing?

Does this answer your question? [generate days from date range](https://stackoverflow.com/questions/2157282/generate-days-from-date-range) — HoldOffHunger, May 25 '20 at 19:14
Thanks for the link. Unfortunately it's not quite what I'm after; I'm looking to do a join. — djs, May 25 '20 at 19:28
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, May 25 '20 at 20:02

score 0 · Accepted Answer · answered May 25 '20 at 19:19

0

When doing a left join, the table on the left hand side of the join is the one that returns all the rows and only the matching rows from the right hand side table. Because you need all dates, it will be table A on the left hand side.

Also, you have to be careful with the filters because it has to be with the join to limit data joined from the right hand side table otherwise it will cut the data from overall output.

SELECT 
  A.DATE
, B.STORE
, B.PRODUCT
, B.UNITS_SOLD
FROM A AS DATES
LEFT JOIN B AS SALES ON DATES.DATE = SALES.DATE 
                    AND B.STORE = 1 
                    AND B.PRODUCT = 'Beans'

answered May 25 '20 at 19:19

VTi

1,309
6
14

Thanks - unfortunately if I follow your suggestion I now get all the dates that aren't present in SALES. So I get 2019-01-02, but not 2019-01-01 or 2019-01-03. – djs May 25 '20 at 19:26
1

EDIT: Sorry I was wrong - I was ordering by the columns that had nulls in and I was only seeing the null results. Your solution works. Thanks! – djs May 25 '20 at 19:34
@djs - I would have been scratching my head if it was not working because it was straight forward. Please considering up-voting the answer if it helped you. – VTi May 25 '20 at 19:39
Also, your table aliases are more like the actual table names and actual table names the other way round. Standardize them please. – VTi May 25 '20 at 19:41

score 0 · Answer 2 · answered May 25 '20 at 19:33

0

Actually you have to left join your DATES table with SALES table.

SELECT 
  A.DATE
, B.STORE
, B.PRODUCT
, B.UNITS_SOLD
FROM A AS DATES
LEFT JOIN B AS SALES ON DATES.DATE = SALES.DATE;

answered May 25 '20 at 19:33

Shreyas B

475
2
11

How do I do a LEFT JOIN in MySQL, where I have missing key values in the RIGHT table?

2 Answers2