How to JOIN by date columns? 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
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