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

我试图比较两个表，SQL Server，以验证一些数据。我想从两个表中返回数据在其中一个或另一个中的所有行。本质上，我想展示所有的差异。我需要检查这样做的三段数据，FirstName, LastName和产品。

我对SQL相当陌生，似乎我找到的很多解决方案都过于复杂了。我不需要担心null。

我是这样开始的:

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)

但是我总是得到0结果，当我知道td中至少有1行不是在d中。

编辑:

好吧，我想我明白了。至少在我几分钟的测试中，它似乎工作得足够好。

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

这基本上会告诉我测试数据中哪些是真实数据中没有的。这完全可以满足我的需求。

当前回答

你可以用except，比如这样:

-- DB1..Tb1 have values than DB2..Tb1 not have
Select Col1,Col2,Col3 From DB1..Tb1
except
Select Col1,Col2,Col3 From DB2..Tb1
-- Now we change order
-- DB2..Tb1 have values than DB1..Tb1 not have
Select Col1,Col2,Col3 From DB2..Tb1
except
Select Col1,Col2,Col3 From DB1..Tb1

2019-08-15 17:41:52

其他回答

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

2015-12-07 09:42:37

如果你有表A和表B，都有列C，下面是在表A中出现而在表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

在这种情况下，您需要知道的是，当一个记录可以在a中找到，但在B中找不到，那么来自B的列将为NULL，类似地，对于那些存在于B而不在a中的记录，来自a的列将为NULL。

2010-01-16 15:33:54

这将做的技巧，类似于Tiago的解决方案，返回“源”表以及。

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中，您将有表引用。

2014-11-02 01:53:50

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

2010-01-16 15:50:31

如果你想要得到哪些列值是不同的，你可以使用实体-属性-值模型:

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小提琴示例

2013-07-26 06:14:47

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

推荐文章

最新文章

标签