Oracle outer partition join

with my_tab as --subquery_factoring_clause

(

select  extract( day from arrivaldate) d_num  ,h.name hotel_name,rt.name room_name,count(*) cnt  from reservation r, block b, hotel h,roomtype rt

where r.blockid = b.blockid

and b.hotelid = h.hotelid

and b.roomtypeid = rt.roomtypeid

and b.hotelid = rt.hotelid

and b.eventid  = rt.eventid

and arrivaldate >=  to_date('01.01.2012','dd.mm.yyyy')

and arrivaldate <=  to_date('01.02.2012','dd.mm.yyyy')

group by extract( day from arrivaldate) ,h.name,rt.name

),

d_days as

(

  select level d_num from dual connect by level <=31

)

select d_days.d_num,my_tab.hotel_name,my_tab.room_name,nvl(my_tab.cnt,0)

from    d_days left outer join my_tab

on d_days.d_num= my_tab.d_num

order by 2

There would be data only for that hotel and rooms where we have reservations on ohter columns it would be NULL

with my_tab as --subquery_factoring_clause

(

select  extract( day from arrivaldate) d_num  ,h.name hotel_name,rt.name room_name,count(*) cnt  from reservation r, block b, hotel h,roomtype rt

where r.blockid = b.blockid

and b.hotelid = h.hotelid

and b.roomtypeid = rt.roomtypeid

and b.hotelid = rt.hotelid

and b.eventid  = rt.eventid

and arrivaldate >=  to_date('01.01.2012','dd.mm.yyyy')

and arrivaldate <=  to_date('01.02.2012','dd.mm.yyyy')

group by extract( day from arrivaldate) ,h.name,rt.name

),

d_days as

(

  select level d_num from dual connect by level <=31

)

select d_days.d_num,my_tab.hotel_name,my_tab.room_name,nvl(my_tab.cnt,0)

from    d_days left outer join my_tab

partition by (my_tab.hotel_name,my_tab.room_name) -- partition outer join

on d_days.d_num= my_tab.d_num

order by 2