比较查询以比较两个SQL Server表 [英] Comparison Query to Compare Two SQL Server Tables

问题描述

我想知道如何比较两个不同的数据库表记录.我的意思是，我将比较两个数据库表，它们可能具有不同的列名但具有相同的数据.但是其中一个表可能比另一个表具有更多的记录，因此我想看看这两个表之间的区别是什么.为此，如何编写sql查询?仅供参考:这两个数据库都在同一个SQL Server实例下.

I would like to know how to compare two different database table records. What I mean is I will compare two database tables which may have different column names but same data. But one of them may have more records than the other one so I want to see what the difference is between those two tables. To do that how to write the sql query ? FYI : these two databases are under the same SQL Server instance.

Table1
------+---------
|name |lastname|
------+---------
|John |rose    |
------+---------
|Demy |Sanches |
------+---------

Table2
------+----------
|name2|lastname2|
------+----------
|John |rose     |
------+----------
|Demy |Sanches  |
------+----------
|Ruby |Core     |
------+----------

然后，在比较表1和表2之后，它应该从表2返回 Ruby Core.

Then when after comparing table 1 and table 2, it should return Ruby Core from Table2.

推荐答案

如果执行从T1到T2的外部联接，则可以通过在T2值中查找空值来找到前者中不在后者中的行.T2与T1的外部联接将为您提供T2中的行.将两者结合在一起，您将得到很多……类似:

If you do an outer join from T1 to T2 you can find rows in the former that are not in the latter by looking for nulls in the T2 values, similarly an outer join of T2 to T1 will give you rows in T2. Union the two together and you get the lot... something like:

SELECT 'Table1' AS TableName, name, lastname FROM
    Table1 OUTER JOIN Table2 ON Table1.name = Table2.name2 
                             AND Table1.lastname = Table2.lastname
WHERE Table2.name2 IS NULL
UNION
SELECT 'Table2' AS TableName, name2 as name, lastname2 as lastname FROM
    Table2 OUTER JOIN Table1 ON Table2.name2 = Table1.name 
                             AND Table2.lastname2 = Table1.lastname
WHERE Table1.name IS NULL

那是我的头上的东西-我有点生锈:)

That's off the top of my head - and I'm a bit rusty :)

这篇关于比较查询以比较两个SQL Server表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！