代码之家 › 专栏 › 技术社区 › Casey

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

sql-server-2005 sql-server-2008 sql-server sql

155

Casey · 技术社区 · 14 年前

我正在尝试比较两个表,即SQL Server,以验证一些数据。我想返回两个表中的所有行,其中数据要么在一个表中,要么在另一个表中。本质上,我想展示所有的差异。我需要检查三个数据,名字,姓氏和产品。

我对SQL还比较陌生,似乎我正在寻找的许多解决方案都过于复杂化了。我不必为空值担心。

我开始尝试这样的方法:

SELECT DISTINCT [First Name], [Last Name], [Product Name] FROM [Temp Test Data]
WHERE ([First Name] NOT IN (SELECT [First Name] 
FROM [Real Data]))

不过,我很难再继续下去了。

谢谢!

编辑:

根据@treaschf的回答,我尝试使用以下查询的变体:

SELECT td.[First Name], td.[Last Name], td.[Product Name]
FROM [Temp Test Data] td FULL OUTER JOIN [Data] AS d 
ON td.[First Name] = d.[First Name] AND td.[Last Name] = d.[Last Name] 
WHERE (d.[First Name] = NULL) AND (d.[Last Name] = NULL)

但当我知道td中至少有一行不在d中时,我会一直得到0个结果。

编辑:

好吧,我想我明白了。至少在我几分钟的测试中,它似乎已经足够好用了。

SELECT [First Name], [Last Name]
FROM [Temp Test Data] AS td
WHERE (NOT EXISTS
        (SELECT [First Name], [Last Name]
         FROM [Data] AS d
         WHERE ([First Name] = td.[First Name]) OR ([Last Name] = td.[Last Name])))

这基本上会告诉我测试数据中的内容不在我的真实数据中。这完全可以满足我的需要。

10 回复 | 直到 6 年前

171

treaschf 14 年前

如果你有桌子 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

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

212

Jeffrey Kemp 12 年前

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

Maisie John 11 年前

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

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

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

bilelovitch 9 年前

要获得两个表之间的所有差异,可以像我一样使用此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
;

roman 11 年前

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

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

knut 6 年前

@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)

Kango_V 14 年前

试试这个:

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])

阅读起来简单多了。

Community Alok Save 7 年前

这会起到同样的作用 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中,您将有表引用。

thomas398 11 年前

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

--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

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

Tiago Moutinho 10 年前

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

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

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