To answer the "why do these exist" question, Alex Keh, PM for .NET providers has this to say:About a decade ago, Microsoft introduced a new technology called Language Integrated Query (LINQ). LINQ allowed you to compose a query once and have it work against any data source, Oracle DB included. To do this, LINQ creates an expression tree. Each data source provider takes the expression tree and converts it into the DB's native SQL.
Most of the time, LINQ generates expression trees that can be converted into standard SQL. One of the times it doesn't is when it generates the CROSS APPLY keywords. This expression is only supported by SQL Server, not Oracle.
Thus, we support CROSS APPLY in order to:
1) Ensure LINQ customers can use Oracle without hitting an error if this SQL is generated
2) Help customers migrate their SQL from SQL Server to Oracle
So really it's just there to improve compatibility with other systems. If you're working on a homegrown Oracle application you can probably ignore them.
That said there may be cases where using these may make queries easier to understand and/or execute faster.
For example, consider the following question:
"Show me the two highest paid employees hired after 1 Jan 2007 per department along with the department details."
Now, you could write this with a join like:
select * from (
select d.*, first_name, last_name, salary,
row_number() over (partition by d.department_id order by salary desc) rn
from hr.departments d
join hr.employees e
on e.department_id = d.department_id
where e.hire_date >= date'2007-01-01'
where rn <= 2
order by department_id, salary desc;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID FIRST_NAME LAST_NAME SALARY RN
30 Purchasing 114 1,700 Karen Colmenares 2,500 1
50 Shipping 121 1,500 Kevin Mourgos 5,800 1
50 Shipping 121 1,500 Anthony Cabrio 3,000 2
60 IT 103 1,400 Bruce Ernst 6,000 1
60 IT 103 1,400 Diana Lorentz 4,200 2
80 Sales 145 2,500 Gerald Cambrault 11,000 1
80 Sales 145 2,500 Eleni Zlotkey 10,500 2
100 Finance 108 1,700 Luis Popp 6,900 1
Or using cross apply, you could do the following instead:
select d.*, first_name, last_name, salary
from hr.departments d
cross apply (
select first_name, last_name, salary
from hr.employees e
where e.department_id = d.department_id
and e.hire_date >= date'2007-01-01'
order by e.salary desc
fetch first 2 rows only
order by 1, salary desc;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID FIRST_NAME LAST_NAME SALARY
30 Purchasing 114 1,700 Karen Colmenares 2,500
50 Shipping 121 1,500 Kevin Mourgos 5,800
50 Shipping 121 1,500 Anthony Cabrio 3,000
60 IT 103 1,400 Bruce Ernst 6,000
60 IT 103 1,400 Diana Lorentz 4,200
80 Sales 145 2,500 Gerald Cambrault 11,000
80 Sales 145 2,500 Eleni Zlotkey 10,500
100 Finance 108 1,700 Luis Popp 6,900
Personally I think the cross apply query is slightly easier to understand. But this does come down to personal preference and knowledge of cross apply! ;)
See also: https://explainextended.com/2009/07/16/inner-join-vs-cross-apply/ https://oracle-base.com/articles/12c/lateral-inline-views-cross-apply-and-outer-apply-joins-12cr1#cross-apply-join