With Table_1 as
      (select 1 as id1 from Dual 
       union 
       select 2 as id1 from Dual),
     
     Table_2 as
      (select 2 as id2 from Dual 
       union 
       select 3 as id2 from Dual)

--- 1 вариант
select * from Table_1 inner join Table_2
              on Table_1.id1 = Table_2.id2      
--- 2 вариант
select * from Table_1, Table_2
       where Table_1.id1 = Table_2.id2 
    

select fact.*, map.*
  from fact
       join dim_1
          on dim_1.id = fact.dim_1_id
       join dim_n
          on dim_1.dim_n_id = dim_n.id
       left join map
          on fact.type in ('A', 'B', 'C')
         and ((map.category = 'FACT VALUE' and map.value = fact.value)
           or (map.category = 'DETAILED VALUE' and map.value = dim_n.value));

select *
from Table_1
cross join Table_2
...
cross join Table_n
where <каша из предикатов>