CROSS/OUTER APPLY in MySQL
Solution 1
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
Solution 2
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.
Solution 3
Based on @lujas szozda answer:
For the CROSS APPLY:
SELECT ORD.ID
,ORD.NAME
,ORD.DATE
,ORD_HIST.VALUE
FROM ORD 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
/* ON 1=1 is just a trick to join to whatever the LATERAL returns */
For the OUTER APPLY:
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
/* ON 1=1 is just a trick to join to whatever the LATERAL returns */
Related videos on Youtube
Comments
-
hoz almost 4 years
I need to use CROSS APPLY in MySQL (EC2 RDS MySQL instance). Looks like MySQL doesn't recognise the CROSS APPLY Syntax. Can someone help me please?
Here's the query.
SELECT ORD.ID ,ORD.NAME ,ORD.DATE ,ORD_HIST.VALUE FROM ORD CROSS APPLY ( SELECT TOP 1 ORD_HISTORY.VALUE FROM ORD_HISTORY WHERE ORD.ID = ORD_HISTORY.ID AND ORD.DATE <= ORD_HISTORY.DATE ORDER BY ORD_HISTORY.DATE DESC ) ORD_HIST
-
zhongxiao37 almost 5 yearsI tried the 1st solution is really slow for 7k records in ORD table. Really miss SQL SERVER.
-
Yván Ecarri almost 4 yearsCan you explain what do you mean with
LEFT JOIN LATERAL () ON 1=1
? That syntax does not work in MySql 8.0.18 -
Lukasz Szozda almost 4 years@YvánEcarri Sure, added an example to illustrate this scenario.