A report by month and cabin should show every month for every cabin, with a zero where nothing was sold. An outer join to a list of months fills the gaps for the data as a whole, but not for each cabin separately: a month that has economy tickets but no business tickets still appears only for economy. A partitioned outer join solves this by doing the outer join once for each group.
Code for This Guide
The main example is in the examples/joins folder of the Oracle Database 26ai code repository on GitHub, with its output. It queries NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
It comes from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
PARTITION BY goes after the table or inline view that holds the sparse data, on one side of an outer join.
Syntax:
select ... from sparse_data partition by (expr [, ...]) right join dense_list on condition -- or the other way round select ... from dense_list left join sparse_data partition by (expr [, ...]) on condition
Oracle splits the sparse data by the partition expression and performs the outer join separately for each partition, so every partition is completed with every row of the dense list.
The Gap Problem
The source counts tickets by cabin and booking month. The month list comes from a VALUES table constructor, which builds a small table inline. A normal right join to it gives:
Example:
-- without PARTITION BY, months missing for one cabin do not appear for it
select t.cabin, m.month, count(t.ticket_id) as tickets
from (select k.ticket_id, k.cabin, to_char(b.booked_at, 'YYYY-MM') as month
from tickets k join bookings b on b.booking_id = k.booking_id) t
right join (values ('2025-10'), ('2025-11'), ('2025-12')) m (month) on m.month = t.month
group by t.cabin, m.month
order by t.cabin, m.month;Output:
CABIN MONTH TICKETS ___________ __________ __________ BUSINESS 2025-12 36 ECONOMY 2025-10 11 ECONOMY 2025-11 126 ECONOMY 2025-12 221
Business class has no tickets in October and November, but those months do not appear for it. The outer join had nothing to fill: each month already matched some ticket, an economy one.
The Partitioned Outer Join
Adding PARTITION BY (t.cabin) after the sparse side makes Oracle do the right join once for business and once for economy.
Example:
-- one row for every cabin and every month, even months without tickets in that cabin
select t.cabin, m.month, count(t.ticket_id) as tickets
from (select k.ticket_id, k.cabin, to_char(b.booked_at, 'YYYY-MM') as month
from tickets k join bookings b on b.booking_id = k.booking_id) t
partition by (t.cabin)
right join (values ('2025-10'), ('2025-11'), ('2025-12')) m (month) on m.month = t.month
group by t.cabin, m.month
order by t.cabin, m.month;Output:
CABIN MONTH TICKETS ___________ __________ __________ BUSINESS 2025-10 0 BUSINESS 2025-11 0 BUSINESS 2025-12 36 ECONOMY 2025-10 11 ECONOMY 2025-11 126 ECONOMY 2025-12 221 6 rows selected.
Each cabin now has all three months. In the added rows, the partition column, cabin, is filled in, while the other columns of the sparse side are NULL. That is why the query counts t.ticket_id rather than using count(*): COUNT of a column ignores the NULL, giving 0 instead of 1.
Things to Know
- The partition column must come from the sparse side and appear in the PARTITION BY on that side; Oracle fills it in for the generated rows.
- The dense list can be any table, view, or subquery: a calendar table, a CONNECT BY LEVEL or recursive WITH series, or a VALUES constructor as here.
- Without partitioned outer joins, the same result needs a cross join of every cabin with every month, followed by an outer join to the data, which is longer and reads the data twice.
Related Guides
- Oracle SQL Query to Use LEFT OUTER JOIN to Include All Records from Left Table
- How to Query Hierarchies with CONNECT BY in Oracle
- How to Pivot Rows into Columns with PIVOT in Oracle
Conclusion
A partitioned outer join adds PARTITION BY to the sparse side of an outer join, so Oracle completes each group with every row of a dense list, such as every month for every cabin. Count a column of the sparse side to get zeros for the generated rows, and generate the dense list with a calendar table, a row generator, or a VALUES constructor.
