Is there any alternative for OUTER APPLY in Oracle?

sql-server oracle outer-apply
user1389591 picture user1389591 · Jul 7, 2015 · Viewed 10.3k times · Source

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?

Answer

Markus Winand picture Markus Winand · Jul 7, 2015

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/

Related questions

How do I list all the columns in a table?

For the various popular database systems, how do you list all the columns in a table?

Read More
Is there a combination of "LIKE" and "IN" in SQL?

In SQL I (sadly) often have to use "LIKE" conditions due to databases that violate nearly every rule of normalization. I can't change that right now. But that's irrelevant to the question. Further, I often use conditions like WHERE something …

Read More
Can a foreign key be NULL and/or duplicate?

Please clarify two things for me: Can a Foreign key be NULL? Can a Foreign key be duplicate? As fair as I know, NULL shouldn't be used in foreign keys, but in some application of mine I'm able to input …

Read More