oracle oracle找出2个表之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13631224/
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
oracle find difference between 2 tables
提问by user441521
I have 2 tables that are the same structure. One is a temp one and the other is a prod one. The entire data set gets loaded each time and sometimes this dataset will have deleted records from the prior datasets. I load the dataset into temp table first and if any records were deleted I want to deleted them from the prod table also.
我有 2 个结构相同的表。一个是临时的，另一个是刺激的。每次都会加载整个数据集，有时这个数据集会从先前的数据集中删除记录。我首先将数据集加载到临时表中，如果删除了任何记录，我也想从 prod 表中删除它们。
So how can I find the records that exist in prod but not in temp? I tried outer join but it doesn't seem to be working. It's returning all the records from the table in the left or right depending on doing left or right outer join.
那么我怎样才能找到存在于 prod 但不在 temp 中的记录呢？我尝试了外连接，但它似乎不起作用。它根据执行左外连接或右外连接从左侧或右侧的表中返回所有记录。
I then also want to delete those records in the prod table.
然后我还想删除 prod 表中的那些记录。
回答by Justin Cave
One way would be to use the MINUSoperator
一种方法是使用MINUS运算符
SELECT * FROM table1
MINUS
SELECT * FROM table2
will show all the rows in table1that do not have an exact match in table2(you can obviously specify a smaller column list if you are only interested in determining whether a particular key exists in both tables).
将显示table1其中没有完全匹配的所有行table2（如果您只对确定两个表中是否存在特定键感兴趣，您显然可以指定一个较小的列列表）。
Another would be to use a NOT EXISTS
另一种方法是使用 NOT EXISTS
SELECT *
FROM table1 t1
WHERE NOT EXISTS( SELECT 1
FROM table2 t2
WHERE t1.some_key = t2.some_key )
回答by Sid
MINUS can work here The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:
MINUS 可以在这里工作 以下语句将结果与 MINUS 运算符组合在一起，该运算符仅返回第一个查询返回的行，而不返回第二个查询返回的行：
SELECT * FROM prod
MINUS
SELECT * FROM temp;
Minus will only work if the table structure is same
减号仅在表结构相同时才有效
回答by Mike Christensen
How about something like:
怎么样：
SELECT * FROM ProdTable WHERE ID NOT IN
(select ID from TempTable);
It'd work the same as a DELETEstatement as well:
它的工作方式也与DELETE语句相同：
DELETE FROM ProdTable WHERE ID NOT IN
(select ID from TempTable);
