Outer_Apply_01

View All Scripts Login to Run Script

Script Name Outer_Apply_01
Description Example for using OUTER APPLY to outer join with a table function. See more details on OUTER APPLY in this blog article: https://technology.amis.nl/2013/06/26/oracle-database-12c-joining-and-outer-joining-with-collections/
Area SQL General / SQL Query
Contributor Lucas Jellema
Created Sunday October 18, 2015

Statement 1
Create the String Table used for returning the collection (of employee names) from the Table Function
Create String Collection Type
```
create or replace type string_tbl as table of varchar2(200)
```
```
Type created.
```

Statement 2

Create the Table Function that retrieves the last names of all employees in the department indicates by p_deptno. This function returns a collection - using the string_tbl collection type.

Create Table Function

create or replace 
 function employees_in_department
 ( p_deptno in number
 ) return string_tbl
 as
   l_enames string_tbl;
 begin
   select cast(collect(last_name) as string_tbl)
   into   l_enames
   from   hr.employees
   where  department_id = p_deptno
   ;
   return l_enames;
end;

Function created.

Statement 3

This statement outer joins all departments with the result (a string_tbl of employee names) from the table function. Note that when a department does not have any employees - and the string_tbl is empty - the department is still present in the result set produced by this query. That is the effect of the OUTER keyword in the statement.

Perform Outer Apply

SELECT d.*
,      staff.*
FROM   hr.departments d
       OUTER APPLY
       employees_in_department(d.department_id) staff

 
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID COLUMN_VALUE
10 Administration 200 1700 Whalen
20 Marketing 201 1800 Hartstein
20 Marketing 201 1800 Fay
30 Purchasing 114 1700 Raphaely
30 Purchasing 114 1700 Khoo
30 Purchasing 114 1700 Baida
30 Purchasing 114 1700 Tobias
30 Purchasing 114 1700 Himuro
30 Purchasing 114 1700 Colmenares
40 Human Resources 203 2400 Mavris
50 Shipping 121 1500 Weiss
50 Shipping 121 1500 Fripp
50 Shipping 121 1500 Kaufling
50 Shipping 121 1500 Vollman
50 Shipping 121 1500 Mourgos
50 Shipping 121 1500 Nayer
50 Shipping 121 1500 Mikkilineni
50 Shipping 121 1500 Landry
50 Shipping 121 1500 Markle
50 Shipping 121 1500 Bissot
50 Shipping 121 1500 Atkinson
50 Shipping 121 1500 Marlow
50 Shipping 121 1500 Olson
50 Shipping 121 1500 Mallin
50 Shipping 121 1500 Rogers
50 Shipping 121 1500 Gee
50 Shipping 121 1500 Philtanker
50 Shipping 121 1500 Ladwig
50 Shipping 121 1500 Stiles
50 Shipping 121 1500 Seo
50 Shipping 121 1500 Patel
50 Shipping 121 1500 Rajs
50 Shipping 121 1500 Davies
50 Shipping 121 1500 Matos
50 Shipping 121 1500 Vargas
50 Shipping 121 1500 Taylor
50 Shipping 121 1500 Fleaur
50 Shipping 121 1500 Sullivan
50 Shipping 121 1500 Geoni
50 Shipping 121 1500 Sarchand
50 Shipping 121 1500 Bull
50 Shipping 121 1500 Dellinger
50 Shipping 121 1500 Cabrio
50 Shipping 121 1500 Chung
50 Shipping 121 1500 Dilly
50 Shipping 121 1500 Gates
50 Shipping 121 1500 Perkins
50 Shipping 121 1500 Bell
50 Shipping 121 1500 Everett
50 Shipping 121 1500 McCain
50 Shipping 121 1500 Jones
50 Shipping 121 1500 Walsh
50 Shipping 121 1500 Feeney
50 Shipping 121 1500 OConnell
50 Shipping 121 1500 Grant
60 IT 103 1400 Hunold
60 IT 103 1400 Ernst
60 IT 103 1400 Austin
60 IT 103 1400 Pataballa
60 IT 103 1400 Lorentz
70 Public Relations 204 2700 Baer
80 Sales 145 2500 Russell
80 Sales 145 2500 Partners
80 Sales 145 2500 Errazuriz
80 Sales 145 2500 Cambrault
80 Sales 145 2500 Zlotkey
80 Sales 145 2500 Tucker
80 Sales 145 2500 Bernstein
80 Sales 145 2500 Hall
80 Sales 145 2500 Olsen
80 Sales 145 2500 Cambrault
80 Sales 145 2500 Tuvault
80 Sales 145 2500 King
80 Sales 145 2500 Sully
80 Sales 145 2500 McEwen
80 Sales 145 2500 Smith
80 Sales 145 2500 Doran
80 Sales 145 2500 Sewall
80 Sales 145 2500 Vishney
80 Sales 145 2500 Greene
80 Sales 145 2500 Marvins
80 Sales 145 2500 Lee
80 Sales 145 2500 Ande
80 Sales 145 2500 Banda
80 Sales 145 2500 Ozer
80 Sales 145 2500 Bloom
80 Sales 145 2500 Fox
80 Sales 145 2500 Smith
80 Sales 145 2500 Bates
80 Sales 145 2500 Kumar
80 Sales 145 2500 Abel
80 Sales 145 2500 Hutton
80 Sales 145 2500 Taylor
80 Sales 145 2500 Livingston
80 Sales 145 2500 Johnson
90 Executive 100 1700 King
90 Executive 100 1700 Kochhar
90 Executive 100 1700 De Haan
100 Finance 108 1700 Greenberg
100 Finance 108 1700 Faviet
100 Finance 108 1700 Chen
100 Finance 108 1700 Sciarra
100 Finance 108 1700 Urman
100 Finance 108 1700 Popp
110 Accounting 205 1700 Higgins
110 Accounting 205 1700 Gietz
120 Treasury  - 1700  - 
130 Corporate Tax  - 1700  - 
140 Control And Credit  - 1700  - 
150 Shareholder Services  - 1700  - 
160 Benefits  - 1700  - 
170 Manufacturing  - 1700  - 
180 Construction  - 1700  - 
190 Contracting  - 1700  - 
200 Operations  - 1700  - 
210 IT Support  - 1700  - 
220 NOC  - 1700  - 
230 IT Helpdesk  - 1700  - 
240 Government Sales  - 1700  - 
250 Retail Sales  - 1700  - 
260 Recruiting  - 1700  - 
270 Payroll  - 1700  - 

122 rows selected.

DEPARTMENT_ID	DEPARTMENT_NAME	MANAGER_ID	LOCATION_ID	COLUMN_VALUE
10	Administration	200	1700	Whalen
20	Marketing	201	1800	Hartstein
20	Marketing	201	1800	Fay
30	Purchasing	114	1700	Raphaely
30	Purchasing	114	1700	Khoo
30	Purchasing	114	1700	Baida
30	Purchasing	114	1700	Tobias
30	Purchasing	114	1700	Himuro
30	Purchasing	114	1700	Colmenares
40	Human Resources	203	2400	Mavris
50	Shipping	121	1500	Weiss
50	Shipping	121	1500	Fripp
50	Shipping	121	1500	Kaufling
50	Shipping	121	1500	Vollman
50	Shipping	121	1500	Mourgos
50	Shipping	121	1500	Nayer
50	Shipping	121	1500	Mikkilineni
50	Shipping	121	1500	Landry
50	Shipping	121	1500	Markle
50	Shipping	121	1500	Bissot
50	Shipping	121	1500	Atkinson
50	Shipping	121	1500	Marlow
50	Shipping	121	1500	Olson
50	Shipping	121	1500	Mallin
50	Shipping	121	1500	Rogers
50	Shipping	121	1500	Gee
50	Shipping	121	1500	Philtanker
50	Shipping	121	1500	Ladwig
50	Shipping	121	1500	Stiles
50	Shipping	121	1500	Seo
50	Shipping	121	1500	Patel
50	Shipping	121	1500	Rajs
50	Shipping	121	1500	Davies
50	Shipping	121	1500	Matos
50	Shipping	121	1500	Vargas
50	Shipping	121	1500	Taylor
50	Shipping	121	1500	Fleaur
50	Shipping	121	1500	Sullivan
50	Shipping	121	1500	Geoni
50	Shipping	121	1500	Sarchand
50	Shipping	121	1500	Bull
50	Shipping	121	1500	Dellinger
50	Shipping	121	1500	Cabrio
50	Shipping	121	1500	Chung
50	Shipping	121	1500	Dilly
50	Shipping	121	1500	Gates
50	Shipping	121	1500	Perkins
50	Shipping	121	1500	Bell
50	Shipping	121	1500	Everett
50	Shipping	121	1500	McCain
50	Shipping	121	1500	Jones
50	Shipping	121	1500	Walsh
50	Shipping	121	1500	Feeney
50	Shipping	121	1500	OConnell
50	Shipping	121	1500	Grant
60	IT	103	1400	Hunold
60	IT	103	1400	Ernst
60	IT	103	1400	Austin
60	IT	103	1400	Pataballa
60	IT	103	1400	Lorentz
70	Public Relations	204	2700	Baer
80	Sales	145	2500	Russell
80	Sales	145	2500	Partners
80	Sales	145	2500	Errazuriz
80	Sales	145	2500	Cambrault
80	Sales	145	2500	Zlotkey
80	Sales	145	2500	Tucker
80	Sales	145	2500	Bernstein
80	Sales	145	2500	Hall
80	Sales	145	2500	Olsen
80	Sales	145	2500	Cambrault
80	Sales	145	2500	Tuvault
80	Sales	145	2500	King
80	Sales	145	2500	Sully
80	Sales	145	2500	McEwen
80	Sales	145	2500	Smith
80	Sales	145	2500	Doran
80	Sales	145	2500	Sewall
80	Sales	145	2500	Vishney
80	Sales	145	2500	Greene
80	Sales	145	2500	Marvins
80	Sales	145	2500	Lee
80	Sales	145	2500	Ande
80	Sales	145	2500	Banda
80	Sales	145	2500	Ozer
80	Sales	145	2500	Bloom
80	Sales	145	2500	Fox
80	Sales	145	2500	Smith
80	Sales	145	2500	Bates
80	Sales	145	2500	Kumar
80	Sales	145	2500	Abel
80	Sales	145	2500	Hutton
80	Sales	145	2500	Taylor
80	Sales	145	2500	Livingston
80	Sales	145	2500	Johnson
90	Executive	100	1700	King
90	Executive	100	1700	Kochhar
90	Executive	100	1700	De Haan
100	Finance	108	1700	Greenberg
100	Finance	108	1700	Faviet
100	Finance	108	1700	Chen
100	Finance	108	1700	Sciarra
100	Finance	108	1700	Urman
100	Finance	108	1700	Popp
110	Accounting	205	1700	Higgins
110	Accounting	205	1700	Gietz
120	Treasury	-	1700	-
130	Corporate Tax	-	1700	-
140	Control And Credit	-	1700	-
150	Shareholder Services	-	1700	-
160	Benefits	-	1700	-
170	Manufacturing	-	1700	-
180	Construction	-	1700	-
190	Contracting	-	1700	-
200	Operations	-	1700	-
210	IT Support	-	1700	-
220	NOC	-	1700	-
230	IT Helpdesk	-	1700	-
240	Government Sales	-	1700	-
250	Retail Sales	-	1700	-
260	Recruiting	-	1700	-
270	Payroll	-	1700	-

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database