MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE ORD(ID INT
    ,NAME TEXT
    ,DATE DATE);

INSERT INTO ORD VALUES (1, 'a', '20200101'),(2,'b','20200101'),(3, 'c','20200101');

Records: 3  Duplicates: 0  Warnings: 0


CREATE TABLE ORD_HISTORY(ID INT
    ,VALUE INT
    ,DATE DATE);

INSERT INTO ORD_HISTORY VALUES (1, 100, '20200501'),(1,200,'20200401'),(1, 300,'20200301');

Records: 3  Duplicates: 0  Warnings: 0


select *
FROM ORD


ID
NAME
DATE


1
a
2020-01-01

2
b
2020-01-01

3
c
2020-01-01


select *
FROM ORD_HISTORY


ID
VALUE
DATE


1
100
2020-05-01

1
200
2020-04-01

1
300
2020-03-01


SELECT ORD.ID
    ,ORD.NAME
    ,ORD.DATE
    ,ORD_HIST.VALUE
FROM ORD,
LATERAL (
    SELECT ORD_HISTORY.VALUE
    FROM ORD_HISTORY
    WHERE ORD.ID = ORD_HISTORY.ID
        AND ORD.DATE <= ORD_HISTORY.DATE
    ORDER BY ORD_HISTORY.DATE DESC
    LIMIT 1
    ) ORD_HIST


ID
NAME
DATE
VALUE


1
a
2020-01-01
100


SELECT ORD.ID
    ,ORD.NAME
    ,ORD.DATE
    ,ORD_HIST.VALUE
FROM ORD LEFT JOIN LATERAL (
    SELECT ORD_HISTORY.VALUE
    FROM ORD_HISTORY
    WHERE ORD.ID = ORD_HISTORY.ID
        AND ORD.DATE <= ORD_HISTORY.DATE
    ORDER BY ORD_HISTORY.DATE DESC
    LIMIT 1
    ) ORD_HIST ON 1=1


ID
NAME
DATE
VALUE


1
a
2020-01-01
100

2
b
2020-01-01
null

3
c
2020-01-01
null