返回两个表之间差异的SQL查询

如果你有桌子 A 和 B ,两个都有colum C ,这是表中的记录 一 但不在 B 以下内容:

SELECT A.*
FROM A
    LEFT JOIN B ON (A.C = B.C)
WHERE B.C IS NULL

要通过单个查询获得所有差异,必须使用完全联接,如下所示:

SELECT A.*, B.*
FROM A
    FULL JOIN B ON (A.C = B.C)
WHERE A.C IS NULL OR B.C IS NULL

在这种情况下,您需要知道的是,当在 一 ,但不在 乙 ,而不是来自 乙 将为空,对于存在于 乙 而不在 一 ,列来自 一 将为空。

(   SELECT * FROM table1
    EXCEPT
    SELECT * FROM table2)  
UNION ALL
(   SELECT * FROM table2
    EXCEPT
    SELECT * FROM table1) 

我知道这可能不是一个流行的答案,但我同意@randy minder在需要更复杂的比较时使用第三方工具。

这里的这个特定案例很简单,对于这种情况,不需要这样的工具,但是如果引入更多的列、两个服务器上的数据库、更复杂的比较标准等,这很容易变得复杂。

这些工具有很多,比如 ApexSQL Data Diff 或 Quest Toad 你可以在试用模式下使用它们来完成工作。

要获得两个表之间的所有差异,可以像我一样使用此SQL请求:

SELECT 'TABLE1-ONLY' AS SRC, T1.*
FROM (
      SELECT * FROM Table1
      EXCEPT
      SELECT * FROM Table2
      ) AS T1
UNION ALL
SELECT 'TABLE2-ONLY' AS SRC, T2.*
FROM (
      SELECT * FROM Table2
      EXCEPT
      SELECT * FROM Table1
      ) AS T2
;

如果要获取不同的列值,可以使用实体属性值模型:

declare @Data1 xml, @Data2 xml

select @Data1 = 
(
    select * 
    from (select * from Test1 except select * from Test2) as a
    for xml raw('Data')
)

select @Data2 = 
(
    select * 
    from (select * from Test2 except select * from Test1) as a
    for xml raw('Data')
)

;with CTE1 as (
    select
        T.C.value('../@ID', 'bigint') as ID,
        T.C.value('local-name(.)', 'nvarchar(128)') as Name,
        T.C.value('.', 'nvarchar(max)') as Value
    from @Data1.nodes('Data/@*') as T(C)    
), CTE2 as (
    select
        T.C.value('../@ID', 'bigint') as ID,
        T.C.value('local-name(.)', 'nvarchar(128)') as Name,
        T.C.value('.', 'nvarchar(max)') as Value
    from @Data2.nodes('Data/@*') as T(C)     
)
select
    isnull(C1.ID, C2.ID) as ID, isnull(C1.Name, C2.Name) as Name, C1.Value as Value1, C2.Value as Value2
from CTE1 as C1
    full outer join CTE2 as C2 on C2.ID = C1.ID and C2.Name = C1.Name
where
not
(
    C1.Value is null and C2.Value is null or
    C1.Value is not null and C2.Value is not null and C1.Value = C2.Value
)

SQL FIDDLE EXAMPLE

@erikkallen答案的简单变体,显示行所在的表:

(   SELECT 'table1' as source, * FROM table1
    EXCEPT
    SELECT * FROM table2)  
UNION ALL
(   SELECT 'table2' as source, * FROM table2
    EXCEPT
    SELECT * FROM table1) 

如果你出错了

使用union、intersect或except运算符组合的所有查询在其目标列表中的表达式数必须相等。

那么它可能有助于增加

(   SELECT 'table1' as source, * FROM table1
    EXCEPT
    SELECT 'table1' as source, * FROM table2)  
UNION ALL
(   SELECT 'table2' as source, * FROM table2
    EXCEPT
    SELECT 'table2' as source, * FROM table1) 

试试这个:

SELECT 
    [First Name], [Last Name]
FROM 
    [Temp Test Data] AS td EXCEPTION JOIN [Data] AS d ON 
         (d.[First Name] = td.[First Name] OR d.[Last Name] = td.[Last Name])

阅读起来简单多了。

这会起到同样的作用 Tiago 的解决方案,同时返回“source”表。

select [First name], [Last name], max(_tabloc) as _tabloc
from (
  select [First Name], [Last name], 't1' as _tabloc from table1
  union all
  select [First name], [Last name], 't2' as _tabloc from table2
) v
group by [Fist Name], [Last name]
having count(1)=1

结果将包含表之间的差异,在列tabloc中,您将有表引用。

对于一个简单的冒烟测试,您要确保两个表匹配,而不必担心列名:

--ensure tables have matching records
Select count (*) from tbl_A
Select count (*) from tbl_B

--create temp table of all records in both tables
Select * into #demo from tbl_A 
Union All
Select * from tbl_B

--Distinct #demo records = Total #demo records/2 = Total tbl_A records = total tbl_B records
Select distinct * from #demo 

您可以轻松地编写存储过程来比较一批表。

有一个与左联接和大数据的完全联接有关的性能问题。

在我看来,这是最好的解决方案:

select [First Name], count(1) e from (select * from [Temp Test Data] union all select * from [Temp Test Data 2]) a group by [First Name] having e = 1