Is there any alternative for OUTER APPLY in Oracle?

Question

In the following example, I pass tbA.ID to tbC query. In this case, I used OUTER APPLY operator of SqlServer.

SELECT 
  ...
FROM (SELECT ID FROM TableA ...) tbA
OUTER APPLY (SELECT ... FROM TableB tbB WHERE tbA.ID = tbB.ID) tbC
...

In Oracle, we don't have the OUTER APPLY operator. So, how can I pass a value (tbA.ID) from the left side query to the right side query (tbC) of the join without modifying the structure of my query?

Is there any alternative for OUTER APPLY in Oracle?

Solution 1

SQL Servers outer apply is similar to the SQL Standards lateral. Oracle supports lateral since 12c(*).

Instead of outer apply you would use left join lateral in standard SQL or cross join lateral if you want to omit the ON/USING clauses.

Footnote: (*) before version 12c, Oracle "unsupported" lateral when enabling a trace event. See https://jonathanlewis.wordpress.com/2011/01/31/ansi-outer/

Solution 2

Edit according to comments:

In Oracle 12 OUTER APPLY is supported (probably as part of SQL standard). Since your SQL generated by entity Framework, all you need to do is connect to Oracle and look how generated query output looks like. I feel that your question is based on fear, "how is it going to work in Oracle?". Run the code and see.

Other than that, Oracle inline queries work just like tables. Your question is, " are there alternatives...?" - yes, see below:

SELECT 
  ...
FROM 
    (SELECT ID FROM TableA ...) tbA left join
    (SELECT ID FROM TableB ...) tbB On tbA.ID = tbB.ID
 ...