By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1
(`DATE` date, `ID` varchar(4), `NAME` varchar(6))
;
INSERT INTO table1
(`DATE`, `ID`, `NAME`)
VALUES
('2007-09-01', '1', 'Apple'),
('2007-10-01', '2', 'Pear'),
('2007-11-01', '3', 'Banana'),
('2007-12-01', NULL, 'Cherry'),
('2008-01-01', '11', 'Kiwi')
;
CREATE TABLE table2
(`DATE` date, `ID` int, `NAME` varchar(6), `Sales` int)
;
INSERT INTO table2
(`DATE`, `ID`, `NAME`, `Sales`)
VALUES
('2007-09-01', 1, 'Apple', 200),
('2007-10-01', 2, 'Pear', 100),
('2007-11-01', 3, 'Banana', 300),
('2007-12-01', 4, 'Cherry', 500)
;
SELECT *
FROM table1 t1 LEFT JOIN table2 t2
ON t1.date = t2.date
AND (t1.id = t2.id OR (t1.id IS NULL AND t1.name = t2.name));
| DATE | ID | NAME | DATE | ID | NAME | Sales |
|---|---|---|---|---|---|---|
| 2007-09-01 | 1 | Apple | 2007-09-01 | 1 | Apple | 200 |
| 2007-10-01 | 2 | Pear | 2007-10-01 | 2 | Pear | 100 |
| 2007-11-01 | 3 | Banana | 2007-11-01 | 3 | Banana | 300 |
| 2007-12-01 | null | Cherry | 2007-12-01 | 4 | Cherry | 500 |
| 2008-01-01 | 11 | Kiwi | null | null | null | null |