How to Replicate CROSS APPLY Functionality in MySQL?

CROSS/OUTER APPLY in MySQL

MySQL does not natively support CROSS APPLY syntax, which can be a challenge when attempting to perform certain operations. This question centers around finding a MySQL equivalent to the following CROSS APPLY 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

While MySQL lacks direct CROSS APPLY support, there are alternative ways to achieve its functionality. The closest approximation is to use a correlated subquery as a predicate in a join query, as seen below:

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
        )

If, like in this case, only a single field from the target table is needed, the join with subquery can be omitted and the subquery can be placed 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

By using these approaches, MySQL users can achieve similar functionality to CROSS APPLY without compromising the accuracy or efficiency of their queries.

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