How Can I Achieve the Functionality of CROSS APPLY in MySQL?

Cross/Outer Apply in MySQL

While MySQL supports a subset of ANSI SQL features, CROSS APPLY is not one of them. To achieve similar functionality, alternative approaches must be employed.

Direct Approximation: Correlated Subquery in Join

Your closest direct approximation involves a join with a correlated subquery in 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
        )

Simplified Alternative: Direct Correlated Subquery

If you require only one field from the target table, you can use a correlated subquery 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

This approach allows you to retrieve the desired value without the need for a join.

The above is the detailed content of How Can I Achieve the Functionality of CROSS APPLY in MySQL?. For more information, please follow other related articles on the PHP Chinese website!