Thanks for the question, Sunil Kumar.

Asked: July 28, 2016 - 1:11 am UTC

Last updated: May 19, 2021 - 5:24 pm UTC

Version: 12c, 11g, 10g

Viewed 50K+ times! This question is

You Asked

Hi,

I have learned that we have CROSS APPLY and OUTER APPLY in 12c. However, I see results are same for CROSS APPLY and INNER JOIN, OUTER APPLY and LEFT / RIGHT OUTER JOIN.

So when INNER JOIN and LEFT/RIGHT OUTER JOIN are ANSI Standard and yielding same results as CROSS APPLY and OUTER APPLY, why these two were introduced in 12c and purpose of the same.

Syntax and usage understood.

Could you please help in understanding the purpose.

Best Regards,
Sunil Kumar Noothi.
Amazing Fan of Concept KISS Series (Keep it Simple SQL) :)

and Chris said...

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 
) e
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

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

dense_rank vs row_number

Robert, August 02, 2016 - 3:37 pm UTC

I don't have any comment on CROSS APPLY, but if you want the top two salaries, I'd suggest that using dense_rank may be better. If two or more employees tied for first or second place, dense_rank would reveal that; row_number would hide it.

August 02, 2016 - 4:40 pm UTC

Certainly something you need to consider - it depends upon what your exact requirements are!

Thank you

Sunil Kumar Noothi, August 03, 2016 - 6:43 am UTC

Thank you for clarifying the details.

Easy CROSS APPLY

Duke Ganote, June 06, 2017 - 4:55 pm UTC

I'm noticing examples where CROSS APPLY does seem clearer. For example, consider two tables A and T where there's an exact join between two tables on column PK1, but a "closest match" for column PK2 (which I'll calculate based on the ASCII value of PK2).

https://livesql.oracle.com/apex/livesql/file/content_E2VGT4YF1N3RI7009MLYO8M2S.html

SET UP for table creation

WITH 
Torso ( pk1, pk2 ) AS ( 
SELECT 'A', 'B' FROM DUAL UNION ALL 
SELECT 'B', 'B' FROM DUAL UNION ALL 
SELECt 'C', 'A' FROM DUAL 
), 
appendage ( pk1, pk2 ) AS ( 
SELECT 'A', 'A' FROM DUAL UNION ALL 
SELECT 'A', 'C' FROM DUAL UNION ALL 
SELECT 'A', 'D' FROM DUAL UNION ALL 
SELECT 'B', 'B' FROM DUAL UNION ALL 
SELECT 'B', 'C' FROM DUAL UNION ALL 
SELECt 'C', 'A' FROM DUAL 
), 
T AS ( 
SELECT t.* 
     , ASCII(pk2) as pk2ascii 
  FROM Torso T 
), 
A AS ( 
SELECT t.* 
     , ASCII(pk2) as pk2ascii 
  FROM appendage T 
)

Cross apply seems to be built for low-volume work based on the NESTED LOOP I see:

SELECT t.pk1, t.pk2 t_pk2, aa.pk2 a_pk2
  FROM T
  CROSS APPLY ( SELECT a.pk1, a.pk2
                  FROM A
                 WHERE a.pk1 = t.pk1
                 ORDER BY ABS ( t.pk2ascii - a.pk2ascii ) ASC
                        , t.pk2ascii, a.pk2ascii
                FETCH FIRST 1 ROWS ONLY
              ) aa
 ORDER BY 1, 2 ;

P T A
- - -
A B A
B B B
C A A

Elapsed: 00:00:00.60

Execution Plan
-------------------------------------
Plan hash value: 3948154422

-------------------------------------
| Id  | Operation                   |
-------------------------------------
|   0 | SELECT STATEMENT            |
|   1 |  SORT ORDER BY              |
|   2 |   NESTED LOOPS              |
|   3 |    TABLE ACCESS FULL        |
|   4 |    VIEW                     |
|*  5 |     VIEW                    |
|*  6 |      WINDOW SORT PUSHED RANK|
|*  7 |       TABLE ACCESS FULL     |
-------------------------------------

Analytic functions are more verbose inner/outer queries but better for larger data sets based on the observed HASH JOIN:

SELECT pk1, t_pk2, a_pk2
  FROM (
SELECT t.pk1, t.pk2 t_pk2, a.pk2 a_pk2
     , ROW_NUMBER()OVER
        ( PARTITION BY t.pk1
              ORDER BY ABS ( t.pk2ascii - a.pk2ascii ) ASC
                     , t.pk2ascii, a.pk2ascii
        ) AS r#
  FROM T
  LEFT OUTER
  JOIN A
    ON A.pk1 = T.pk1
) WHERE r# =1
 ORDER BY 1, 2;

P T A
- - -
A B A
B B B
C A A

Elapsed: 00:00:00.48

Execution Plan
-----------------------------------
Plan hash value: 899162677

-----------------------------------
| Id  | Operation                 |
-----------------------------------
|   0 | SELECT STATEMENT          |
|   1 |  SORT ORDER BY            |
|*  2 |   VIEW                    |
|*  3 |    WINDOW SORT PUSHED RANK|
|*  4 |     HASH JOIN OUTER       |
|   5 |      TABLE ACCESS FULL    |
|   6 |      TABLE ACCESS FULL    |
-----------------------------------

June 07, 2017 - 2:01 pm UTC

Nice analysis. Though strictly speaking the queries aren't equivalent. Either you should OUTER APPLY or change the join in the analytic query to an inner join.

CROSS APPLY across DBMS

Duke Ganote, June 07, 2017 - 3:15 pm UTC

Thank you. David Ballantyne's winning query uses CROSS APPLY for the "Speed Phreak#2: FIFO Inventory" competition held back in 2009:
https://www.simple-talk.com/sql/performance/set-based-speed-phreakery-the-fifo-stock-inventory-sql-problem/

However, I found the observed performance in SQL Server 2012 for his query (which deals with million-record data set) doesn't translate well to Oracle 12c. Different underlying mechanism/implementatoin, no doubt, though I haven't looked at the SQL Server execution plan yet.

June 08, 2017 - 1:34 am UTC

Thanks for the link and the input.

Bon-Minh Lam, May 19, 2021 - 3:01 pm UTC

Thanks for the excellent explanation although I was trying to get "educated" on OUTER APPLY and not CROSS APPLY. But I guess the OUTER APPLY is analog. In summary, can we make these statements?

1. The Oracle way in the example uses analytical function to extract the data which are based on 2 different granularity levels, in this example on employ key and on department key, within one single SELECT COLUMNS clause

2. The LINQ way allows us to combine data from from 2 result sets, one based on department key, one based on employee key, without using any grouping or analytical function

May 19, 2021 - 5:24 pm UTC

OUTER APPLY is the "outer-join" version of CROSS APPLY.

I'm not sure I understand your statements.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.

Breadcrumb

Question and Answer