同样地；第1-16行的样本数据。您可能需要的查询从第17行开始。

SQL> with
  2  tab1 (col) as
  3    (select 'mg/kl'     from dual union all
  4     select 'k/mg'      from dual union all
  5     select 'l/g/kg'    from dual union all
  6     select 'umol_Ulp'  from dual union all
  7     select '10*12_/KG' from dual union all
  8     select 'a/L/G'     from dual
  9    ),
 10  tab2 (col) as
 11    (select '10*12_/kg' from dual union all
 12     select '1/L/G'     from dual union all
 13     select 'PG/7@R'    from dual union all
 14     select 'KG/CM/L'   from dual union all
 15     select 'l/g/kg'    from dual
 16    )
 17  select a.col, b.col,
 18    case when lower(a.col) = lower(b.col) then
 19              'exists in both tables ' ||
 20              case when a.col = b.col then 'and exact match'
 21                   else 'but different'
 22              end
 23         when a.col is null then 'missing in table 1'
 24         when b.col is null then 'missing in table 2'
 25    end differences
 26  from tab1 a full outer join tab2 b on lower(a.col) = lower(b.col);

COL       COL       DIFFERENCES
--------- --------- -------------------------------------
mg/kl               missing in table 2
k/mg                missing in table 2
l/g/kg    l/g/kg    exists in both tables and exact match
umol_Ulp            missing in table 2
10*12_/KG 10*12_/kg exists in both tables but different
a/L/G               missing in table 2
          KG/CM/L   missing in table 1
          PG/7@R    missing in table 1
          1/L/G     missing in table 1

9 rows selected.

SQL>

你可以用 full outer join 与 upper 以及 replace 连接条件中的函数如下：

Select t1.str, t2.str,
       Case when t1.str is not null and t2.str is not null then 'exist in both table'
             when t1.str is not null then 'missing in table2'
             Else 'missing in table1'
       End as differences
From table1 t1 full join table2 t2
  On upper(replace(t1.str,' ','') = upper(replace(t2.str,' ','')

尝试一下下面的sql，看看是否有效。否则，即使两个表中都有值，您是否可以共享这些值，这些值会显示为“表2中缺少”？

Select t1.str, t2.str,
       Case when nvl(t1.str,'X') != 'X' and nvl(t2.str,'X') != 'X' then 'exist in both table'
             when nvl(t1.str,'X') != 'X' then 'missing in table2'
             Else 'missing in table1'
       End as differences
From table1 t1 full join table2 t2
  On upper(trim(t1.str)) = upper(trim(t2.str))