How to JOIN by date columns?

Question

I have two tables with the structure of

CREATE TABLE table1
(
Month date,
Value1 varchar(255),
PRIMARY KEY(Month)
) ENGINE=InnoDB

CREATE TABLE table2
(
Day date,
Value2 varchar(255),
PRIMARY KEY(Day)
) ENGINE=InnoDB

In table1, the date values are stored in the form of

2018-04-01
2018-03-01
2018-02-01
2018-01-01

and in table2

2018-04-01
2018-04-02
2018-04-03
2018-04-04
2018-04-05

How can I JOIN two tables to have the month value (Value1 from table1) for each row of table2?

The desirable result

Day           Month        Value1        Value2
2018-04-01    2018-04-01   
2018-04-02    2018-04-01
2018-04-03    2018-04-01
2018-04-04    2018-04-01
2018-04-05    2018-04-01

Answer 1

Just use LEFT JOIN + DATE_FORMAT:

SELECT
  t2.Day,
  t1.Month,
  t1.Value1,
  t2.Value2
FROM Table2 t2 LEFT JOIN Table1 t1 ON DATE_FORMAT(t2.Day, '%Y-%m-01') = t1.Month

http://www.sqlfiddle.com/#!9/920c79/1

Answer 2

If you aren't sure that all of your dates in the second table end are on the first day, you can be sure the comparison on year/month with EXTRACT( YEAR_MONTH FROM datecol) like this,

SELECT
  t2.Day,
  t1.Month,
  t1.Value1,
  t2.Value2
FROM Table2 t2
LEFT JOIN Table1 t1
  ON EXTRACT(YEAR_MONTH FROM t2.Day) = EXTRACT(YEAR_MONTH FROM t1.Month)

It is likely faster regardless, unless you have an index on t1.month

Answer 3

You can treat the DATEs as strings:

WHERE LEFT(t1.Month, 7) = LEFT(t2.Day, 7)