如何在Oracle中逐列比较两个表

Question

我在两个不同的Oracle数据库中有两个相似的表。

例如：我的表名为EMPLOYEE，主键为员工ID。这两个数据库中都有相同的表和列（假设有50列），并且这两个数据库已经连接。

我想逐列比较这两个表，并找出哪些记录不匹配。我想知道两个表中每行的具体列不匹配。

- Defendore

---

为什么这个答案是可接受的？它没有完成最困难的部分，即指定未匹配的列。 - Stephanie Page

Answer 1

select *
from 
(
( select * from TableInSchema1
  minus 
  select * from TableInSchema2)
union all
( select * from TableInSchema2
  minus
  select * from TableInSchema1)
)

如果你想通过查询来解决这个问题，should do the trick就可以了。

- mcabral

---

您可能还想在每个查询中添加一列，以指示数据来自何处。例如：“select 1 schema1Or2, TableInSchema1.* from TableInSchema1 minus...”。然后在末尾按某些值和该新列排序，例如“order by 2, 3, 4, 5, 1 desc”。然后您将（可能）获得相关行相邻，并且很明显有什么不同而缺少了什么。 - Jon Heller

@jon，你是在建议SELECT 2 SCHEMA1or2,*对于减号的另一半吗？如果你添加一个常数，那么每行都不会匹配。尝试使用同一张表。从属性a中选择Schema1or2中的1，a.然后减去从属性b中选择的2，b. <--将产生所有的行。而且你得到了两个赞，这真的有点可怕。 - Stephanie Page

@Stephanie，我的意思是在前两个查询中添加“select 1”，并在后两个查询中添加“select 2”。 - Jon Heller

我会把它作为一个全新的答案。只需将其标记为 CW，以便它不像是剽窃他人的声望。当你获得更多的声望时，编辑答案，这是一个很好的建议。 - Stephanie Page

Answer 2

作为一种替代方案，可以避免对每个表进行两次完整扫描，并且通过值的组合轻松确定哪个表具有更多行：

SELECT col1
     , col2
     -- (include all columns that you want to compare)
     , COUNT(src1) CNT1
     , COUNT(src2) CNT2
  FROM (SELECT a.col1
             , a.col2
             -- (include all columns that you want to compare)
             , 1 src1
             , TO_NUMBER(NULL) src2
          FROM tab_a a
         UNION ALL
        SELECT b.col1
             , b.col2
             -- (include all columns that you want to compare)
             , TO_NUMBER(NULL) src1
             , 2 src2
          FROM tab_b b
       )
 GROUP BY col1
        , col2
HAVING COUNT(src1) <> COUNT(src2) -- only show the combinations that don't match

感谢这里的贡献：http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1417403971710

- Patrick Marchand

Answer 3

虽然这不会很快，而且你需要输入很多内容（除非你从user_tab_columns生成SQL），但以下是我在需要逐行和逐列比较两个表时使用的方法。

此查询将返回：

• 存在于table1中但不存在于table2中的所有行
• 存在于table2中但不存在于table1中的所有行
• 同时存在于两个表中，但至少有一个列具有不同值的所有行

(相同的行将被排除)。

"PK"是组成您的主键的列。 "a"将包含A，如果当前行存在于table1中。 "b"将包含B，如果当前行存在于table2中。

select pk
      ,decode(a.rowid, null, null, 'A') as a
      ,decode(b.rowid, null, null, 'B') as b
      ,a.col1, b.col1
      ,a.col2, b.col2
      ,a.col3, b.col3
      ,...
  from table1 a 
  full outer 
  join table2 b using(pk)
 where decode(a.col1, b.col1, 1, 0) = 0
    or decode(a.col2, b.col2, 1, 0) = 0
    or decode(a.col3, b.col3, 1, 0) = 0
    or ...;

编辑 添加示例代码以展示评论中描述的差异。 每当其中一个值包含NULL时，结果将不同。

with a as(
   select 0    as col1 from dual union all
   select 1    as col1 from dual union all
   select null as col1 from dual
)
,b as(
   select 1    as col1 from dual union all
   select 2    as col1 from dual union all
   select null as col1 from dual
)   
select a.col1
      ,b.col1
      ,decode(a.col1, b.col1, 'Same', 'Different') as approach_1
      ,case when a.col1 <> b.col1 then 'Different' else 'Same' end as approach_2       
  from a,b
 order 
    by a.col1
      ,b.col1;    




col1   col1_1   approach_1  approach_2
====   ======   ==========  ==========
  0        1    Different   Different  
  0        2    Different   Different  
  0      null   Different   Same         <--- 
  1        1    Same        Same       
  1        2    Different   Different  
  1      null   Different   Same         <---
null       1    Different   Same         <---
null       2    Different   Same         <---
null     null   Same        Same       

- Ronnis

---

嗨Ronnis，你能描述一下你的代码中decode命令是做什么的吗？ - M_Mogharrabi

这是解决空值问题的一种方法。例如，如果其中一个表中的“col1”为空，我将无法通过使用“a.col1 <> b.col1”检测到不匹配。我正在利用DECODE将NULL视为等于NULL的事实。 - Ronnis

它可能足够快。我刚刚在12个模式上执行了此操作，共计500个表格，总共300万行，跨越数据库链接，并且在不到10分钟的时间内完成了所有表格的比较（并同步目标）。 - Tony BenBrahim

Answer 4

使用减号运算符是可行的，但执行时间较长，这是不可接受的。我有一个类似数据迁移的需求，我用NOT IN运算符来解决。修改后的查询语句是：

select * 
from A 
where (emp_id,emp_name) not in 
   (select emp_id,emp_name from B) 
   union all 
select * from B 
where (emp_id,emp_name) not in 
   (select emp_id,emp_name from A); 

这个查询执行得很快。同时，您可以在选择查询中添加任意数量的列。 唯一的限制是两个表必须具有完全相同的表结构才能执行此操作。

- AKS

Answer 5

SELECT *
  FROM (SELECT   table_name, COUNT (*) cnt
            FROM all_tab_columns
           WHERE owner IN ('OWNER_A')
        GROUP BY table_name) x,
       (SELECT   table_name, COUNT (*) cnt
            FROM all_tab_columns
           WHERE owner IN ('OWNER_B')
        GROUP BY table_name) y
 WHERE x.table_name = y.table_name AND x.cnt <> y.cnt;

- Ahmed Bilal

Answer 6

使用了全外连接 -- 但如果没有匹配，它将不会显示 -

SQL> desc aaa - 这是一个表 名称 是否为空？ 类型

---

A1 数字 B1 VARCHAR2(10)

SQL> 描述 aaav - 这是一个视图 名称 是否为空? 类型

---

A1 数字 B1 VARCHAR2(10)

SQL> 选择 a.column_name,b.column_name from dba_tab_columns a full outer join dba_tab_columns b on a.column_name=b.column_name where a.TABLE_NAME='AAA' and B.table_name='AAAV';

COLUMN_NAME COLUMN_NAME

---

A1 A1 B1 B1

A1 A1 B1 B1

- Vasanth Raju

---

尽管回答的格式和逻辑较差，但仍然点赞，因为在其他任何答案中都没有提到“full outer join”，而它在某些情况下确实是一种非常有价值的替代方案，特别是在作为“natural full join”使用时。这篇博客文章详细阐述了它：https://blog.jooq.org/2020/08/05/use-natural-full-join-to-compare-two-tables-in-sql/ - Tomáš Záluský

Answer 7

尝试使用第三方工具，例如SQL Data Examiner，它可以比较Oracle数据库并显示差异。

- SQLDev