CROSS/OUTER APPLY in MySQL

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

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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

Method 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.

db<>fiddle demo

Method 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 */

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating