如何验证两个表是否具有完全相同的数据? [英] How to verify if two tables have exactly the same data?

问题描述

基本上，我们有一个表(original table)，并且已备份到另一表(backup table)；因此，这两个表具有完全相同的架构.

Basically, we have one table (original table) and it is backed up into another table (backup table); thus the two tables have exactly the same schema.

开头，两个表(original table和backup table)都包含完全相同的数据集.经过一段时间后，出于某种原因，我需要验证original table中的数据集是否已更改.

In the beginning, both tables (original table and backup table) contains exactly the same set of data. After some time for some reason, I need to verify whether dataset in the original table has changed or not.

为此，我必须将original table中的数据集与backup table进行比较.

In order to do this, I have to compare the dataset in the original table against the backup table.

假设original table具有以下架构:

create table LemmasMapping (
   lemma1 int,
   lemma2 int,
   index ix_lemma1 using btree (lemma1),
   index ix_lemma2 using btree (lemma2)
)

如何实现数据集比较?

更新:该表没有主键.它只是存储两个ID之间的映射.

Update: the table does not have a primary key. It simply stores mappings between two ids.

推荐答案

我会写三个查询.

1. 内部联接，用于拾取两个表中主键都存在的行，但是其他一个或多个列的值存在差异.这将获取原始的已更改行.

1. An inner join to pick up the rows where the primary key exists in both tables, but there is a difference in the value of one or more of the other columns. This would pick up changed rows in original.

左外部联接用于拾取原始表中而不是备份表中的行(即原始行中的主键在备份中不存在).这将返回插入到原始行中的行.

A left outer join to pick up the rows that are in the original tables, but not in the backup table (i.e. a row in original has a primary key that does not exist in backup). This would return rows inserted into the original.

右外部联接用于拾取备份中原来不再存在的行.这将返回从原始行中删除的行.

A right outer join to pick up the rows in backup which no longer exist in the original. This would return rows that have been deleted from the original.

您可以将三个查询合并在一起以返回单个结果集.如果这样做，则需要添加一列以指示其类型(更新，插入或删除).

You could union the three queries together to return a single result set. If you did this you would need to add a column to indicate what type of row it is (updated, inserted or deleted).

稍加努力，您也许可以使用完整的外部联接在一个查询中执行此操作.小心外部联接，因为它们在不同的SQL引擎中的行为不同.谓词放在where子句中，而不是join子句中，有时会使您的外部联接变成内部联接.

With a bit of effort, you might be able to do this in one query using a full outer join. Be careful with outer joins, as they behave differently in different SQL engines. Predicates put in the where clause, instead of the join clause can sometimes turn your outer join into an inner join.

这篇关于如何验证两个表是否具有完全相同的数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！