What is difference between CROSS APPLY and LATERAL in oracle?

user_12937828 0
Viewed 626

I am able to understand the difference between Cross apply and Outer apply. But what is the difference between Cross apply and Lateral? Both seem to correlate an inline view and join the left table with the right table.

oracle
1 Answers

It's not a general answer. It's only an example of something you can do with lateral but not with apply:

you have a table t (has only 1 lines in this example). For each line of t, you make 2 differents types of computations (X and Y) which returns severals (3) lines.

you don't want to make a cross product of X and Y. You want that the first lines X to be next to the first line of Y and so on.

X Y
1 11
2 22
3 33

If you use cross apply. the only way (witout creating subqueries) to that is to add a condition (in the where block)

WITH
    t (X, Y)
    AS
        (SELECT json_array (1, 2, 3), json_array (11, 22, 33) FROM DUAL),
    step2
    AS
        (SELECT t1.idx, t1.x, t2.Y
           FROM t
                CROSS APPLY
                (SELECT *
                   FROM JSON_TABLE (
                            t.X,
                            '$'
                            COLUMNS (
                                NESTED PATH '$[*]'
                                    COLUMNS (idx FOR ORDINALITY, x PATH '$'))))
                t1
                CROSS APPLY
                JSON_TABLE (
                    t.Y,
                    '$'
                    COLUMNS (
                        NESTED PATH '$[*]'
                            COLUMNS (idx FOR ORDINALITY, y PATH '$'))) t2
          WHERE t1.idx = t2.idx)                            --on t1.idx=t2.idx
SELECT step2.x, step2.Y
  FROM step2;

But with lateral you can do that with a jointure. It easier to read.

 WITH
    t (X, Y)
    AS
        (SELECT json_array (1, 2, 3), json_array (11, 22, 33) FROM DUAL),
    step2
    AS
        (SELECT t1.idx, t1.x, t2.Y
           FROM t
                CROSS APPLY
                (SELECT *
                   FROM JSON_TABLE (
                            t.X,
                            '$'
                            COLUMNS (
                                NESTED PATH '$[*]'
                                    COLUMNS (idx FOR ORDINALITY, x PATH '$'))))
                t1
                INNER JOIN
                LATERAL (
                    SELECT *
                      FROM JSON_TABLE (
                               t.Y,
                               '$'
                               COLUMNS (
                                   NESTED PATH '$[*]'
                                       COLUMNS (idx FOR ORDINALITY,
                                                y PATH '$')))--where t1.idx=t2.idx
                                                             ) t2
                    ON t1.idx = t2.idx)
SELECT *
  FROM step2;

code

where the example comes from

user_84580830
Related