Oracle sql, filling the missing values in the join between the table and the dictionary

A request for help in solving problem, example below on image:

Example:

Answer

The data densification technique using “partition by” claude after an “outer join” clause comes very handy for that purpose.

select s.sales_man, nvl(s.name, d.name) name, nvl(s.sales, 0)sales, d.sort_order
from dict d
left join sales s
    partition by (s.sales_man)
on d.name = s.name
order by s.sales_man, d.sort_order
;

demo on db<>fiddle