下面的查询就可以了

with t2 as (
    select c3
      from t2
     where c3 in ('COD1', 'COD2', 'COD3', 'COD4')
    )
   ,t3 as (
    select distinct
           c1
          ,c2
      from t1
   )    
  ,t_all as (
   select t3.c1
         ,t3.c2
         ,t2.c3
     from t3
         ,t2 
   )
   select t1.c1
         ,t1.c2
         ,t_all.c3
     from t_all left join t1 on 
          t1.c1 = t_all.c1 and t1.c2 = t_all.c2 and t1.c3 = t_all.c3

你可以用 cross join 生成行，然后 left join :

select t1.col1, t1.col2, c3.col3
from (select distinct col1 from table1) c1 cross join
     table2 c3 left join
     table1 t1
     on t1.col1 = c1.col1 and t1.col3 = c3.col3
order by c1.col1, c3.col3;

注：奇怪的是你有 NULL 前两列中的值。这样会产生完全重复的行。如果你使用 select c1.col1, t1.col2, c3.col3 第一列中有有效值。

你需要使用 cross join 以及 left join 具体如下：

SQL> with table1 (col1,col2,col3) as
  2  (select '000001','ABC','COD1' from dual union all
  3  select '000001','ABC','COD2' from dual union all
  4  select '000002','BCD','COD3' from dual union all
  5  select '000003','EDF','COD1' from dual union all
  6  select '000003','EDF','COD3' from dual ),
  7  TABLE2 (COL3) AS
  8  (SELECT 'COD1' FROM DUAL UNION ALL
  9  SELECT 'COD2' FROM DUAL UNION ALL
 10  SELECT 'COD3' FROM DUAL UNION ALL
 11  SELECT 'COD4' FROM DUAL UNION ALL
 12  SELECT 'COD5' FROM DUAL)
 13  select t11.col1, T11.COL2, T2.COL3
 14  from (SELECT DISTINCT COL1 FROM TABLE1) T1
 15  CROSS JOIN (SELECT * FROM TABLE2 WHERE COL3 IN (SELECT COL3 FROM TABLE1)) T2
 16  LEFT JOIN TABLE1 T11 ON T11.COL1 = T1.COL1 AND T2.COL3 = T11.COL3
 17  ORDER BY T1.COL1, T2.COL3;

COL1   COL COL3
------ --- ----
000001 ABC COD1
000001 ABC COD2
           COD3
           COD1
           COD2
000002 BCD COD3
000003 EDF COD1
           COD2
000003 EDF COD3

9 rows selected.

SQL>

您正在寻找一个分区外部连接一个“新”的功能从2004年，但仍然没有广泛使用。
我们的期望是 table1 包含的所有值 column3 至少一个值，但 column2 是稀疏的，必须填充(请注意，您甚至不需要 table2 （全部）
这个查询很简单，因为您希望column2为空，所以在添加它时，必须用 case 声明：

select 
  tab.column1, 
  case when tab.column1 is NOT NULL then tab.column2 end column2, 
  c3.column3
from tab
partition by (column2)
right outer join 
(select distinct column3 from tab) c3
on tab.column3 = c3.column3
order by tab.column2, c3.column3;

这将按要求生成数据：

COLUMN COL COLU
------ --- ----
000001 ABC COD1
000001 ABC COD2
           COD3
           COD4
           COD1
           COD2
000002 BCD COD3
           COD4
000003 EDF COD1
           COD2
000003 EDF COD3
           COD4
000004 FGH COD1
000004 FGH COD2
000004 FGH COD3
000004 FGH COD4

注意，manuall解决方案（即在分区外部联接之前）是从 table1 -请参见子查询 comb 吼叫。
第二步很简单 outer join 这个 comb 去你的table

with col2 as(
select distinct column2 from tab),
col3 as (
select distinct column3 from tab),
comb as (
select *
from col2 cross join col3)
select tab.column1, tab.column2, comb.column3 from comb
left outer join tab 
on comb.column2 = tab.column2 and comb.column3 = tab.column3
order by comb.column2, comb.column3
;

这就得到了同样的结果。