Analytics - Partitioned Outer Join

View All Scripts Login to Run Script

Script Name Analytics - Partitioned Outer Join
Description SQL from the KISS (Keep It Simply SQL) Analytic video series by Developer Advocate Connor McDonald. This script is demonstrates the partitioned outer join, a means via which to do a "grouped" outer join based on logical subsets of the data. In the sample script, we perform a join on bookings for meetings room, partitioning the result set out for each room Run this script standalone, or take it as part of the complete Analytics class at https://tinyurl.com/devgym-classes
Area SQL General / Analytics
Contributor Connor Mcdonald (Oracle)
Created Friday June 03, 2016

Statement 1
```
drop table bookings purge
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

create table bookings ( hr int, room varchar2(10), who varchar2(10))

Table created.

Statement 3

insert into bookings values(      8, 'Room2' ,     'PETE')

1 row(s) inserted.

Statement 4

insert into bookings values(      9, 'Room1' ,     'JOHN')

1 row(s) inserted.

Statement 5

insert into bookings values(     11, 'Room1' ,     'MIKE')

1 row(s) inserted.

Statement 6

insert into bookings values(     14, 'Room2' ,     'JILL')

1 row(s) inserted.

Statement 7

insert into bookings values(     15, 'Room2' ,     'JANE')

1 row(s) inserted.

Statement 8

insert into bookings values(     16, 'Room1' ,     'SAM')

1 row(s) inserted.

Statement 9
```
drop table hrs purge
```
```
ORA-00942: table or view does not exist
```
More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 10

create table hrs ( hr int )

Table created.

Statement 11

insert into hrs values ( 8)

1 row(s) inserted.

Statement 12

insert into hrs values ( 9)

1 row(s) inserted.

Statement 13

insert into hrs values (10)

1 row(s) inserted.

Statement 14

insert into hrs values (11)

1 row(s) inserted.

Statement 15

insert into hrs values (12)

1 row(s) inserted.

Statement 16

insert into hrs values (13)

1 row(s) inserted.

Statement 17

insert into hrs values (14)

1 row(s) inserted.

Statement 18

insert into hrs values (15)

1 row(s) inserted.

Statement 19

insert into hrs values (16)

1 row(s) inserted.

Statement 20

select * from bookings


HR ROOM WHO
8 Room2 PETE
9 Room1 JOHN
11 Room1 MIKE
14 Room2 JILL
15 Room2 JANE
16 Room1 SAM

6 rows selected.

HR	ROOM	WHO
8	Room2	PETE
9	Room1	JOHN
11	Room1	MIKE
14	Room2	JILL
15	Room2	JANE
16	Room1	SAM

Statement 21

select * from hrs


HR
8
9
10
11
12
13
14
15
16

9 rows selected.

HR
8
9
10
11
12
13
14
15
16

Statement 22

select hrs.hr, t1.room, t1.who
from   hrs, bookings t1
where  hrs.hr = t1.hr(+)
order by 1


HR ROOM WHO
8 Room2 PETE
9 Room1 JOHN
10  -  - 
11 Room1 MIKE
12  -  - 
13  -  - 
14 Room2 JILL
15 Room2 JANE
16 Room1 SAM

9 rows selected.

HR	ROOM	WHO
8	Room2	PETE
9	Room1	JOHN
10	-	-
11	Room1	MIKE
12	-	-
13	-	-
14	Room2	JILL
15	Room2	JANE
16	Room1	SAM

Statement 23

select hrs.hr, t1.room, t1.who
from   bookings t1
partition by  (t1.room) 
right outer join hrs on (hrs.hr = t1.hr)
order by 1,2


HR ROOM WHO
8 Room1  - 
8 Room2 PETE
9 Room1 JOHN
9 Room2  - 
10 Room1  - 
10 Room2  - 
11 Room1 MIKE
11 Room2  - 
12 Room1  - 
12 Room2  - 
13 Room1  - 
13 Room2  - 
14 Room1  - 
14 Room2 JILL
15 Room1  - 
15 Room2 JANE
16 Room1 SAM
16 Room2  - 

18 rows selected.

HR	ROOM	WHO
8	Room1	-
8	Room2	PETE
9	Room1	JOHN
9	Room2	-
10	Room1	-
10	Room2	-
11	Room1	MIKE
11	Room2	-
12	Room1	-
12	Room2	-
13	Room1	-
13	Room2	-
14	Room1	-
14	Room2	JILL
15	Room1	-
15	Room2	JANE
16	Room1	SAM
16	Room2	-

Additional Information

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