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

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
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.
Statement 21
```
select * from hrs
```
HR
8
9
10
11
12
13
14
15
16

9 rows selected.
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.
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	Room2	PETE
9	Room1	JOHN
11	Room1	MIKE
14	Room2	JILL
15	Room2	JANE
16	Room1	SAM

HR
8
9
10
11
12
13
14
15
16

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

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