SQL 如何在oracle中逐列比较两个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3980462/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to compare two tables column by column in oracle
提问by Defendore
I have two similar tables in oracle in two different databases. For example : my table name is EMPLOYEE and primary key is employee id. The same table with same columns(say 50 columns are is avlbl in two databases and two databases are linked.
我在两个不同的数据库中的 oracle 中有两个相似的表。例如：我的表名是 EMPLOYEE，主键是员工 ID。具有相同列的同一个表（比如 50 列是两个数据库中的 avlbl 并且两个数据库是链接的。
I want to compare these two tables column by column and find out which records are not matching. i want the specific column in each row in two tables that are not matching.
我想逐列比较这两个表并找出哪些记录不匹配。我想要两个不匹配的表中每一行的特定列。
回答by mcabral
select *
from
(
( select * from TableInSchema1
minus
select * from TableInSchema2)
union all
( select * from TableInSchema2
minus
select * from TableInSchema1)
)
should do the trick if you want to solve this with a query
如果你想用查询来解决这个问题，应该可以解决这个问题
回答by Patrick Marchand
As an alternative which saves from full scanning each table twice and also gives you an easy way to tell which table had more rows with a combination of values than the other:
作为一种替代方法，它可以避免对每个表进行两次完全扫描，并且还为您提供了一种简单的方法来判断哪个表的值组合比另一个表多：
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
Credit goes here: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1417403971710
信用在这里：http: //asktom.oracle.com/pls/apex/f?p=100:11:0 ::::P11_QUESTION_ID: 1417403971710
回答by Ronnis
It won't be fast, and there will be a lot for you to type (unless you generate the SQL from user_tab_columns), but here is what I use when I need to compare two tables row-by-row and column-by-column.
它不会很快，并且会有很多内容供您输入（除非您从 user_tab_columns 生成 SQL），但这是我需要逐行和逐列比较两个表时使用的 -柱子。
The query will return all rows that
该查询将返回所有行
- Exists in table1 but not in table2
- Exists in table2 but not in table1
- Exists in both tables, but have at least one column with a different value
- 存在于表 1 但不存在于表 2
- 存在于表 2 但不存在于表 1
- 存在于两个表中，但至少有一列具有不同的值
(common identical rows will be excluded).
（将排除常见的相同行）。
"PK" is the column(s) that make up your primary key. "a" will contain A if the present row exists in table1. "b" will contain B if the present row exists in table2.
“PK”是构成主键的列。如果当前行存在于 table1 中，“a”将包含 A。如果当前行存在于 table2 中，“b”将包含 B。
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 ...;
EditAdded example code to show the difference described in comment. Whenever one of the values contains NULL, the result will be different.
编辑添加了示例代码以显示注释中描述的差异。只要其中一个值包含 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
回答by AKS
Using the minusoperator was working but also it was taking more time to execute which was not acceptable.
I have a similar kind of requirement for data migration and I used the NOT INoperator for that.
The modified query is :
使用minus操作符是可行的，但它需要更多的时间来执行，这是不可接受的。我对数据迁移有类似的要求，为此我使用了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);
This query executed fast. Also you can add any number of columns in the select query. Only catch is that both tables should have the exact same table structure for this to be executed.
这个查询执行得很快。您还可以在选择查询中添加任意数量的列。唯一的问题是两个表应该具有完全相同的表结构才能执行。
回答by Ahmed Bilal
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;
回答by Vasanth Raju
Used full outer join -- But it will not show - if its not matched -
使用全外连接——但它不会显示——如果它不匹配——
SQL> desc aaa - its a table Name Null? Type
SQL> desc aaa - 它的表名称为空？类型
A1 NUMBER B1 VARCHAR2(10)
A1 号码 B1 VARCHAR2(10)
SQL> desc aaav -its a view Name Null? Type
SQL> desc aaav -它的视图名称为空？类型
A1 NUMBER B1 VARCHAR2(10)
A1 号码 B1 VARCHAR2(10)
SQL> select 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';
SQL> select a.column_name,b.column_name from dba_tab_columns a full external 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
COLUMN_NAME COLUMN_NAME
A1 A1 B1 B1
A1 A1 B1 B1
回答by SQLDev
Try to use 3rd party tool, such as SQL Data Examinerwhich compares Oracle databases and shows you differences.
尝试使用第 3 方工具，例如SQL Data Examiner，它可以比较 Oracle 数据库并显示差异。
