我试图比较两个表，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])))
这基本上会告诉我测试数据中哪些是真实数据中没有的。这完全可以满足我的需求。
@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)
如果你想要得到哪些列值是不同的，你可以使用实体-属性-值模型:
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小提琴示例
