mysqljoinsubquerycross-apply

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

Solution

  • 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