MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table1 (id text, `date` date, val int);
create table table2 (id text, `date` date, val int);

insert into table1 (id, `date`, val)
values ('sn1', '2010/01/26', 10),
       ('sn1', '2010/01/25', 9),
       ('sn1', '2010/01/21', 8),
       ('sn2', '2010/01/23', 9),
       ('sn2', '2010/01/22', 7),
       ('sn1', '2010/01/19', 10);
insert into table1 (id, `date`, val) values ('sn2', '2010/01/18', 11);
select * from table1 order by 1,2;

insert into table2 (id, `date`, val) values ('sn1', '2010/01/26', 20);
insert into table2 (id, `date`, val) values ('sn2', '2010/01/23', 99);
insert into table2 (id, `date`, val) values ('sn2', '2010/01/17', 50);
insert into table2 (id, `date`, val) values ('sn2', '2010/01/21', 60);
insert into table2 (id, `date`, val) values ('sn1', '2010/01/20', 0);
select * from table2 order by 1,2;


id
date
val


sn1
2010-01-19
10

sn1
2010-01-21
8

sn1
2010-01-25
9

sn1
2010-01-26
10

sn2
2010-01-18
11

sn2
2010-01-22
7

sn2
2010-01-23
9




id
date
val


sn1
2010-01-20
0

sn1
2010-01-26
20

sn2
2010-01-17
50

sn2
2010-01-21
60

sn2
2010-01-23
99


-- applicable for version 5
SELECT t1.id, t1.`date`, t1.val val1, t2.val val2
FROM table1 t1
LEFT JOIN table2 t2 ON t2.`date` >= t1.`date` 
              AND t2.id = t1.id
AND NOT EXISTS (SELECT NULL
                  FROM table1 t3
                  WHERE t3.`date` > t1.`date`
                    AND t2.`date` >= t3.`date`
                    AND t3.id = t1.id)
ORDER BY id, `date`;


id
date
val1
val2


sn1
2010-01-19
10
0

sn1
2010-01-21
8
null

sn1
2010-01-25
9
null

sn1
2010-01-26
10
20

sn2
2010-01-18
11
60

sn2
2010-01-22
7
null

sn2
2010-01-23
9
99


-- needs version 8
WITH 
cte1 AS (SELECT id, `date`, val val1, NULL val2
         FROM table1
        UNION ALL
         SELECT id, `date`, NULL val1, val val2
         FROM table2),
cte2 AS (SELECT id, 
                `date`, 
                val1, 
                CASE WHEN val2 IS NULL
                     THEN LEAD(val2) OVER (PARTITION BY id ORDER BY `date`, val2)
                     ELSE val2
                     END val2
         FROM cte1)
SELECT *
FROM cte2
WHERE val1 IS NOT NULL
ORDER BY id, `date`;


id
date
val1
val2


sn1
2010-01-19
10
0

sn1
2010-01-21
8
null

sn1
2010-01-25
9
null

sn1
2010-01-26
10
20

sn2
2010-01-18
11
60

sn2
2010-01-22
7
null

sn2
2010-01-23
9
99

id	date	val
sn1	2010-01-19	10
sn1	2010-01-21	8
sn1	2010-01-25	9
sn1	2010-01-26	10
sn2	2010-01-18	11
sn2	2010-01-22	7
sn2	2010-01-23	9

id	date	val
sn1	2010-01-20	0
sn1	2010-01-26	20
sn2	2010-01-17	50
sn2	2010-01-21	60
sn2	2010-01-23	99

id	date	val1	val2
sn1	2010-01-19	10	0
sn1	2010-01-21	8	null
sn1	2010-01-25	9	null
sn1	2010-01-26	10	20
sn2	2010-01-18	11	60
sn2	2010-01-22	7	null
sn2	2010-01-23	9	99