CROSS/OUTER APPLY in MySQL
Starting from MySQL 8.0.14 you could use LATERAL
:
A derived table now may be preceded by the LATERAL keyword to specify that it is permitted to refer to (depend on) columns of preceding tables in the same FROM clause. A derived table specified with LATERAL can occur only in a FROM clause, either in a list of tables separated with commas or in a join specification (JOIN, INNER JOIN, CROSS JOIN, LEFT [OUTER] JOIN, or RIGHT [OUTER] JOIN). Lateral derived tables make possible certain SQL operations that cannot be done with nonlateral derived tables or that require less-efficient workarounds
CROSS APPLY () <=> ,LATERAL ()
OUTER APPLY () <=> LEFT JOIN LATERAL () ON 1=1
Support for LATERAL derived tables added to MySQL 8.0.14
And in this case:
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;
If correlated subquery does not return any rows, the main row from main query will be skipped. In such scenario, LEFT JOIN LATERAL
should be used.
db<>fiddle demo
Your closest direct approximation is a join with a correlated sub-query as the predicate.
SELECT
ORD.ID
,ORD.NAME
,ORD.DATE
,ORD_HISTORY.VALUE
FROM
ORD
INNER JOIN
ORD_HISTORY
ON ORD_HISTORY.<PRIMARY_KEY>
=
(SELECT ORD_HISTORY.<PRIMARY_KEY>
FROM ORD_HISTORY
WHERE ORD.ID = ORD_HISTORY.ID
AND ORD.DATE <= ORD_HISTORY.DATE
ORDER BY ORD_HISTORY.DATE DESC
LIMIT 1
)
In your case, however, you only need one field from the target table. This means that you are able to use the correlated sub-query directly in the SELECT statement.
SELECT
ORD.ID
,ORD.NAME
,ORD.DATE
,(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
) AS VALUE
FROM
ORD