sql查询返回两个表之间的差异 [英] sql query to return differences between two tables
问题描述
我正在尝试比较两个表,SQL Server,以验证一些数据.我想从两个表中返回数据位于一个或另一个中的所有行.从本质上讲,我想展示所有的差异.为此,我需要检查三项数据,FirstName、LastName 和 Product.
I am trying to compare two tables, SQL Server, to verify some data. I want to return all the rows from both tables where data is either in one or the other. In essence, I want to show all the discrepancies. I need to check three pieces of data in doing so, FirstName, LastName and Product.
我对 SQL 还很陌生,而且我发现的很多解决方案似乎都使事情变得过于复杂.我不必担心 NULL.
I'm fairly new to SQL and it seems like a lot of the solutions I'm finding are over complicating things. I don't have to worry about NULLs.
我开始尝试这样的事情:
I started by trying something like this:
SELECT DISTINCT [First Name], [Last Name], [Product Name] FROM [Temp Test Data]
WHERE ([First Name] NOT IN (SELECT [First Name]
FROM [Real Data]))
不过,我无法更进一步.
I'm having trouble taking this further though.
谢谢!
根据@treaschf 的回答,我一直在尝试使用以下查询的变体:
Based on the answer by @treaschf I have been trying to use a variation of the following query:
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 中至少有 1 行不在 d 中时,我一直得到 0 个结果.
But I keep getting 0 results back, when I know that there is at least 1 row in td that is not in d.
好的,我想我明白了.至少在我的几分钟测试中,它似乎工作得很好.
Ok, I think I figured it out. At least in my few minutes of testing it seems to work good enough.
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])))
这基本上是要告诉我什么是我的测试数据中不是的真实数据.这对于我需要做的事情来说完全没问题.
This is basically going to tell me what is in my test data that is not in my real data. Which is completely fine for what I need to do.
推荐答案
如果你有表 A
和 B
,都带有 C
,这里是记录,它们出现在表 A
中但不在 B
中:
IF you have tables A
and B
, both with colum C
, here are the records, which are present in table A
but not in B
:
SELECT A.*
FROM A
LEFT JOIN B ON (A.C = B.C)
WHERE B.C IS NULL
要通过单个查询获取所有差异,必须使用完整连接,如下所示:
To get all the differences with a single query, a full join must be used, like this:
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
的列也是如此将为空.
What you need to know in this case is, that when a record can be found in A
, but not in B
, than the columns which come from B
will be NULL, and similarly for those, which are present in B
and not in A
, the columns from A
will be null.
这篇关于sql查询返回两个表之间的差异的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!