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?

Tungsten

Do you have top or ordering in tbc? Can we see whole tbc? – Dominickdominie

What version of Oracle are you using? If you're using 12.1, Oracle does support an outer apply. Otherwise, you'd have to change the structure of the query at least a bit to remove the outer apply. In this case, can't you just do a left outer join where the tbA.id = tbB.id is all or part of the join condition? – Muttonhead

dbaora.com/… – Lecithinase

Unfortunatelly, I can't change the structure of the query, because it's a dynamic query generated by Microsoft Entity Framework from a LINQ query. I need to find another way... :/ – Tungsten

EF does have limitations when it comes to SQL. This is why, if you need to execute highly-custom queries or Stored procedures , etc, you don't use EF – Lecithinase

Answer 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/

Baeza

Good answer. I think, we're wasting time here because query is generated by entity framework and Raffael will not be able to do much about it – Lecithinase

Yes, good answer! @Lecithinase is right. I can change the query a little bit after its generation by entity framework and before sending it to DBMS. I can do it by using DbInterceptor. It's possible to replace one or two keywords, but I can't change structure of a complex query by using Interceptors. People, I thank you very much for your help here! – Tungsten

Therefore, in my case I don't need to use "lateral" in Oracle 12c. Because Oracle 12c supports "outer apply". I don't need any changes or SQL interception if I use Oracle 12c. – Tungsten

Answer 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
 ...

Lecithinase

In this case, the structure was modified. My requisite is not changing structure, unfortunately. – Tungsten

@RaffaelBecharaRameh What was modified? You can write a complex join or query inline and result of it join to another complex result. I just don't see in your case, why not use outer join – Lecithinase

What do you mean by structure? – Dominickdominie

@Lecithinase In your great example, thank you, the APPLY clause was turned into a JOIN. Unfortunatelly, it's not what I need. That's why I said the original structure was changed. I was looking for a kind of "keyword" that replaces Apply in Oracle 11g. As I know, Oracle 12c supports "Apply", but I think I can't change my minimum requirements to 12c. – Tungsten

@RaffaelBecharaRameh To be honest, it is hard to guess what you need. Your question says, "Alternative to outer apply", now you say, you looking for "keyword". But the thing is, Join is both, "alternative" and "keyword". There are execution differences. Outer apply is correlated execution, which may or may not take longer, etc. Then, you said, that EF generates this, so, how can we help it if you have no control of this? If you want guaranteed execution, create stored procedure for Oracle and one for SQL server. We would like to help you but your question and comments are contradicting – Lecithinase

@Lecithinase I appreciate the good contribution you gave me. I thank you very much! I conclude that there's no direct correlative to "apply" in Oracle 11g. I was aware about join, but it's a little different. About EF, if it was just one word, I would replace it before sending it to DBMS, through an Interceptor. I will think in moving to 12c. It supports "apply". – Tungsten