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 <p>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:</p><pre class="brush:php;toolbar:false">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 <p>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:</p><pre class="brush:php;toolbar:false">SELECT
   ORD.ID
  ,ORD.NAME
  ,ORD.DATE
  ,(SELECT ORD_HISTORY.VALUE
      FROM ORD_HISTORY
     WHERE ORD.ID = ORD_HISTORY.ID
       AND ORD.DATE <p>By using these approaches, MySQL users can achieve similar functionality to CROSS APPLY without compromising the accuracy or efficiency of their queries.</p>

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!