Usage of OUTER JOIN PARTITION BY In Oracle

It is possible to make an outer join with partitions in Oracle. Suppose that you have a table that contains workers' work days. If a worker did not work, there is no record in that table, working_time table. If your boss want you to prepare a sheet that contains all workers' working times. You have to put all day entries. If a worker did not work, you simple add "0" working hours for worker. How can you achieve this situation?
Follow the example....

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL>
SQL> DROP TABLE working_time;

DROP TABLE working_time

ORA-00942: table or view does not exist

SQL> DROP TABLE worker;

DROP TABLE worker

ORA-00942: table or view does not exist

SQL> CREATE TABLE worker( id number primary key, name varchar2(32), salary number);

Table created

SQL> INSERT INTO worker VALUES (1, 'Mennan', 1200);

1 row inserted

SQL> INSERT INTO worker VALUES (2, 'Ali', 1500);

1 row inserted

SQL> INSERT INTO worker VALUES (3, 'Selim', 900);

1 row inserted

SQL> INSERT INTO worker VALUES (4, 'Ayse', 1450);

1 row inserted

SQL> INSERT INTO worker VALUES (5, 'Seyyah', 2900);

1 row inserted

SQL> commit;

Commit complete

SQL> SELECT * FROM worker;

ID NAME SALARY
---------- -------------------------------- ----------
1 Mennan 1200
2 Ali 1500
3 Selim 900
4 Ayse 1450
5 Seyyah 2900

SQL> CREATE TABLE working_time( worker_id number references worker, working_date date, working_hour number );

Table created

SQL> INSERT INTO working_time VALUES(1, to_date('01.11.2006','DD.MM.YYYY'), 4);

1 row inserted

SQL> INSERT INTO working_time VALUES(1, to_date('02.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> INSERT INTO working_time VALUES(1, to_date('05.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> INSERT INTO working_time VALUES(5, to_date('01.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> INSERT INTO working_time VALUES(5, to_date('02.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> INSERT INTO working_time VALUES(5, to_date('03.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> INSERT INTO working_time VALUES(5, to_date('04.11.2006','DD.MM.YYYY'), 6);

1 row inserted

SQL> INSERT INTO working_time VALUES(3, to_date('02.11.2006','DD.MM.YYYY'), 6);

1 row inserted

SQL> INSERT INTO working_time VALUES(3, to_date('05.11.2006','DD.MM.YYYY'), 8);

1 row inserted

SQL> commit;

Commit complete

SQL> SELECT * FROM working_time;

WORKER_ID WORKING_DATE WORKING_HOUR
---------- ------------ ------------
1 01.11.2006 4
1 02.11.2006 8
1 05.11.2006 8
5 01.11.2006 8
5 02.11.2006 8
5 03.11.2006 8
5 04.11.2006 6
3 02.11.2006 6
3 05.11.2006 8

9 rows selected

SQL> SELECT * FROM worker;

ID NAME SALARY
---------- -------------------------------- ----------
1 Mennan 1200
2 Ali 1500
3 Selim 900
4 Ayse 1450
5 Seyyah 2900

SQL> SELECT wr.name, wt.working_date, wt.working_hour FROM working_time wt, worker wr WHERE wt.worker_id = wr.id;

NAME WORKING_DATE WORKING_HOUR
-------------------------------- ------------ ------------
Mennan 01.11.2006 4
Mennan 02.11.2006 8
Mennan 05.11.2006 8
Seyyah 01.11.2006 8
Seyyah 02.11.2006 8
Seyyah 03.11.2006 8
Seyyah 04.11.2006 6
Selim 02.11.2006 6
Selim 05.11.2006 8

9 rows selected

SQL> SELECT inn.NAME, tim.time_day, nvl(inn.working_hour, 0) working_hour
2 FROM (SELECT wr.NAME, wt.working_date, wt.working_hour
3 FROM working_time wt, worker wr
4 WHERE wt.worker_id = wr.id) inn PARTITION BY(inn.NAME)
5 RIGHT OUTER JOIN (SELECT to_date('31.10.2006', 'DD.MM.YYYY') + rownum time_day
6 FROM all_objects
7 WHERE rownum <=
8 (SYSDATE - to_date('31.10.2006', 'DD.MM.YYYY'))) tim ON (inn.working_date =
9 tim.time_day)
10 ORDER BY inn.NAME, tim.time_day;

NAME TIME_DAY WORKING_HOUR
-------------------------------- ----------- ------------
Mennan 01.11.2006 4
Mennan 02.11.2006 8
Mennan 03.11.2006 0
Mennan 04.11.2006 0
Mennan 05.11.2006 8
Mennan 06.11.2006 0
Mennan 07.11.2006 0
Mennan 08.11.2006 0
Mennan 09.11.2006 0
Mennan 10.11.2006 0
Mennan 11.11.2006 0
Mennan 12.11.2006 0
Mennan 13.11.2006 0
Mennan 14.11.2006 0
Selim 01.11.2006 0
Selim 02.11.2006 6
Selim 03.11.2006 0
Selim 04.11.2006 0
Selim 05.11.2006 8
Selim 06.11.2006 0
Selim 07.11.2006 0
Selim 08.11.2006 0
Selim 09.11.2006 0
Selim 10.11.2006 0
Selim 11.11.2006 0
Selim 12.11.2006 0
Selim 13.11.2006 0
Selim 14.11.2006 0
Seyyah 01.11.2006 8
Seyyah 02.11.2006 8
Seyyah 03.11.2006 8
Seyyah 04.11.2006 6
Seyyah 05.11.2006 0
Seyyah 06.11.2006 0
Seyyah 07.11.2006 0
Seyyah 08.11.2006 0
Seyyah 09.11.2006 0
Seyyah 10.11.2006 0
Seyyah 11.11.2006 0
Seyyah 12.11.2006 0
Seyyah 13.11.2006 0

NAME TIME_DAY WORKING_HOUR
-------------------------------- ----------- ------------
Seyyah 14.11.2006 0

42 rows selected

SQL> DROP TABLE working_time;

Table dropped

SQL> DROP TABLE worker;

Table dropped

SQL>