oracle sql partition outer join - ghdrako/doc_snipets GitHub Wiki

Using to fill gap in data , especially in dates

ANSI Syntax:

SELECT select_expression FROM   table_reference   PARTITION BY (expr [, expr ]... )   RIGHT OUTER JOIN table_reference
--
SELECT select_expression FROM   table_reference   LEFT OUTER JOIN table_reference   PARTITION BY {expr [,expr ]...)

Note that FULL OUTER JOIN is not supported with a partitioned outer join.

The partitioned outer join (10g) selects the partition key of the outer table even where there are no matching rows

SELECT
   d.DEPTNO,
   e.JOB,
   COUNT(e.EMPNO)
FROM
   EMP e
PARTITION BY
   (e.JOB)
RIGHT JOIN
   DEPT d
ON
   (e.DEPTNO=d.DEPTNO)
GROUP BY
   d.DEPTNO,
   e.JOB
ORDER BY
   d.DEPTNO,
   e.JOB;

In hrs table we have all hours (dense). We want have every combination hours and rooms even if is not booking

select hrs.hr,t1.room,t1.who
from booking t1
partition by (t1.room)
right outer join hrs on (hrs.hr = t1.hr)

Classic way:

with q1 as
(
select distinct t1.room
from   booking t1 
),

q2 as
(
select hrs.hr
     , q1.room
from   timeslots hrs
cross  join q1
)

select q2.hr
     , q2.room
     , t1.who
from   q2
left   join booking t1
on     q2.hr = t1.hr
       and q2.room = t1.room;