How to Replicate CROSS/OUTER APPLY Functionality in MySQL?

CROSS/OUTER APPLY in MySQL

MySQL lacks direct support for the CROSS APPLY syntax, but alternative approaches can be employed to achieve similar functionality.

Nested Sub-query:

In MySQL, a common alternative is to use a correlated sub-query as a predicate in a JOIN statement. This ensures that the sub-query's results are evaluated for each row in the outer query.

Example:

Given 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 <p>The equivalent query using a nested sub-query would be:</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><strong>Direct Sub-query:</strong></p><p>In cases where only a single field from the target table is required, a correlated sub-query can be directly integrated into 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 

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