continuing from sql | how NOT to avoid multiple identical subqueries in the SELECT clause

these won't work if your db is older than 12c. if it is lower, i guess the how NOT to is the how to, not an expert don't quote me on that. and no i don't want to use good ol' normal people joins today, just because.

example table init scripts are at the end

a/ CROSS APLLY

this will return all the rows from left, and at least one has to be present on the other side, i believe this made sense?

SELECT A.ID,
       A.VAL1,
       B.VAL2,
       B.VAL3
FROM   TABLE1 A
       CROSS APPLY (SELECT  ID, VAL2, VAL3
                      FROM  TABLE2 B
                     WHERE  A.ID = B.ID) B;

/*
        ID VAL1        VAL2       VAL3
---------- ----- ---------- ----------
         1 TEST1          2          3
         1 TEST1          4          5
         1 TEST1          6          7
         1 TEST1          8          9
         2 TEST2         12         13

5 rows selected.
*/

b/ OUTER APPLY

all the values from the left will be present, regardless of there existence on the right, you know like left join. this definitely made sense

SELECT A.ID,
       A.VAL1,
       B.VAL2,
       B.VAL3
FROM   TABLE1 A
       OUTER APPLY (SELECT  ID, VAL2, VAL3
                      FROM  TABLE2 B
                     WHERE  A.ID = B.ID) B;

/*
        ID VAL1        VAL2       VAL3
---------- ----- ---------- ----------
         1 TEST1          2          3
         1 TEST1          4          5
         1 TEST1          6          7
         1 TEST1          8          9
         2 TEST2         12         13
         4 TEST4                      
         3 TEST3                      

7 rows selected.
*/

c/ LITERAL inline view

table on the left can be referenced in the FROM clause of the inline reference, you get a correlated cartesian product, if that makes sense

SELECT A.ID,
       A.VAL1,
       B.VAL2,
       B.VAL3
FROM   TABLE1 A,    ---notice the comma
       LATERAL (SELECT  ID, VAL2, VAL3
                  FROM  TABLE2 B
                 WHERE  A.ID = B.ID) B;

/*
        ID VAL1        VAL2       VAL3
---------- ----- ---------- ----------
         1 TEST1          2          3
         1 TEST1          4          5
         1 TEST1          6          7
         1 TEST1          8          9
         2 TEST2         12         13

5 rows selected.
*/

z/ tables used for examples

CREATE TABLE TABLE1 AS
SELECT 1 ID, 'TEST1' VAL1 FROM DUAL;

INSERT ALL
    INTO TABLE1 VALUES(2, 'TEST2')
    INTO TABLE1 VALUES(3, 'TEST3')
    INTO TABLE1 VALUES(4, 'TEST4')
SELECT 1 FROM DUAL; COMMIT;


CREATE TABLE TABLE2 AS
SELECT 1 ID, 2 VAL2, 3 VAL3 FROM DUAL;

INSERT ALL
    INTO TABLE2 VALUES(1, 4, 5)
    INTO TABLE2 VALUES(1, 6, 7)
    INTO TABLE2 VALUES(1, 8, 9)
    INTO TABLE2 VALUES(6, 10, 11)
    INTO TABLE2 VALUES(2, 12, 13)
SELECT 1 FROM DUAL; COMMIT;

should have used the sample HR schema