0

Does anyone knows a equivalent in ORACLE PL/SQL for the SQL Server CROSS APPLY statement? If so what I want is, in a aggregation sub-query alias as a table, just do the aggregation for those IDs and don't do the aggregation for the whole table, before pick up the values that I need.

Quick example:

SELECT A.Val1, B.Val2
FROM TABLE1 A
CROSS APPLY ( SELECT SUM(Val2) AS Val2 FROM Table2 T2 WHERE T2.FK1 = A.PK1) B
Dale K
  • 25,246
  • 15
  • 42
  • 71
Negarrak
  • 375
  • 1
  • 4
  • 11

1 Answers1

0

While I'm not aware of Oracle syntax, you can rewrite your query so that it should be compatible with both:

SELECT A.Val1, B.Val2
FROM TABLE1 A
  left join (
    SELECT t2.FK1, SUM(Val2) AS Val2 FROM Table2 T2 group by T2.FK1
) B on b.FK1 = A.PK1;

This should actually be more efficient in SQL Server, too, as apply tends to be more expensive than join, and should only be used when no other alternatives exist.

Roger Wolf
  • 7,307
  • 2
  • 24
  • 33
  • But, that will calculate all the aggregation for the whole Table2, and then discard which of them enters to the join. That is the what I was trying to avoid.. I have a query that works, however, I was trying to enhance the performance. For 0.8 M records it takes some time.. Thanks for your answer! – Negarrak Jun 04 '20 at 03:05
  • I don't know about SQL Server, but in Oracle the "cross apply" operator will definitely work faster than a standard join. The OP knows what he is talking about. –  Jun 04 '20 at 05:24