CROSS APPLY and OUTER APPLY

Oracle 12c has introduced CROSS APPLY and OUTER APPLY as one of the new features . You can use these constructs to join the results from parent table to a function that would return table value / pipelined functions / subquery . CROSS APPLY is similar to the inner join where as the OUTER APPLY is of left . outer join . 

Let us look at the following example. 

Use Case 1 : To display the student record and their three most recent course registration. 

We will create the following tables. 

Here is the SQL that uses CROSS APPLY to fulfill our use case. 

Use Case 2:  To display ALL the students and  their three most recent course registration.  This list should include any students that do not any course registration as well.

Here is the SQL that uses CROSS APPLY to fulfill our use case. 
As you can see King Tailor has no course registrations , but still displayed.

Note : 
These constructs has been in SQL Server since SQL Server 2005 . 

Here are the examples. 

Example : Using CROSS APPLY

Example : Using OUTER  APPLY

We can also use Table Value Functions in place of subquery . 

Example : Using CROSS  APPLY in TVF ( Table valued Function ) 

Using the above created TVF in CROSS APPLY 

Welcome your comments.