T-SQL：比较两个表 - 第二个表中不存在的记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/372126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
T-SQL: Comparing Two Tables - Records that don't exist in second table
提问by BuddyJoe
If UNION ALLis an additionin T-SQL. What is the equivalent of subtraction?
IfUNION ALL是T-SQL 中的一个加法。减法的等价物是什么？
For example, if I have a table PEOPLEand a table EMPLOYEES. And I know if I remove EMPLOYEESrecords from PEOPLEI will be left with my companies CONTRACTORS.
例如，如果我有一张桌子PEOPLE和一张桌子EMPLOYEES。而且我知道如果我EMPLOYEES从PEOPLE我的公司中删除记录，我将留在我的公司CONTRACTORS。
Is there a way of doing this that is similar to UNION ALL? One where I don't have to specify any field names? The reason I ask is this is just one hypothetical example. I need to do this several times to many different tables. Assume that the schema of EMPLOYEESand PEOPLEare the same.
有没有办法做到这一点，类似于UNION ALL？一个我不必指定任何字段名称的地方？我问的原因是这只是一个假设的例子。我需要对许多不同的表执行多次此操作。假设的模式EMPLOYEES和PEOPLE是相同的。
回答by Eric Ness
You can use the EXCEPT operatorto subtract one set from another. Here's a sample of code using EMPLOYEES and PEOPLE temporary tables. You'll need to use the field names with the EXCEPT operator as far as I know.
您可以使用EXCEPT 运算符从另一个集合中减去一个集合。这是使用 EMPLOYEES 和 PEOPLE 临时表的代码示例。据我所知，您需要将字段名称与 EXCEPT 运算符一起使用。
CREATE TABLE #PEOPLE
(ID INTEGER,
Name NVARCHAR(50))
CREATE TABLE #EMPLOYEE
(ID INTEGER,
Name NVARCHAR(50))
GO
INSERT #PEOPLE VALUES (1, 'Bob')
INSERT #PEOPLE VALUES (2, 'Steve')
INSERT #PEOPLE VALUES (3, 'Jim')
INSERT #EMPLOYEE VALUES (1, 'Bob')
GO
SELECT ID, Name
FROM #PEOPLE
EXCEPT
SELECT ID, Name
FROM #EMPLOYEE
GO
The final query will return the two rows in the PEOPLE table which do not exist in the EMPLOYEE table.
最终查询将返回 PEOPLE 表中 EMPLOYEE 表中不存在的两行。
回答by Tom H
SELECT
P.*
FROM
People P
LEFT OUTER JOIN Employees E ON
E.ID = P.ID -- Or whatever your PK-FK relationship is
WHERE
E.ID IS NULL
For SQL Server this will probably be the most performant way that you can do it.
对于 SQL Server，这可能是您能做到的最高效的方式。
回答by Charles Bretana
Instead of using UNION, use EXCEPT, ( or INTERSECT to get only records in both ) as described in
不使用 UNION，而是使用 EXCEPT,（或 INTERSECT 只获取两者中的记录），如中所述
回答by Greg Dean
SELECT * FROM Table1
WHERE Table1.Key NOT IN (SELECT Table2.Key FROM Table2 WHERE Table2.Key IS NOT NULL)
Added IS NOT NULL to make people happy.
添加了 IS NOT NULL 以使人们高兴。
I would agree with Tom. His version is most likely more efficient. The only possible reason to use mine, might be that it's prettier.
我同意汤姆的看法。他的版本很可能更有效率。使用我的唯一可能的原因可能是它更漂亮。
回答by call me Steve
Unfortunately there is a problem in your design. instead of having two table PEOPLE and CONTRACTOR. You should have a table PEOPLE and another Table TYPE (if some people can have several role another table maybe needed). In your PEOPLE table you make a referece to the TYPE table.
不幸的是，您的设计存在问题。而不是有两个表 PEOPLE 和 CONTRACTOR。你应该有一个表 PEOPLE 和另一个表类型（如果有些人可以扮演多个角色，可能需要另一个表）。在您的 PEOPLE 表中，您可以参考 TYPE 表。
then you requests become
然后你的请求变成
SELECT * from PEOPLE, TYPE
WHERE PEOPLE.type_id = TYPE.id
AND TYPE.name = 'CONTRACTOR'
SELECT * from PEOPLE, TYPE
WHERE PEOPLE.type_id = TYPE.id
AND TYPE.name = 'EMPLOYEE'
(untested)
（未经测试）
回答by xkonnectDeveloper
When I compare tables looking for data that isn't in one that is in the other I typically use SQL Division.
当我比较表以查找不在另一个中的数据时，我通常使用 SQL Division。
select *(or selected matching field)
from tableA as A
where not exist
(select *(or selected matching field)
from tableB as B
where A.key = B.key)
This query will return the results that are in tableA that are not in through the process of division.
此查询将返回 tableA 中未通过除法过程的结果。
select *(or selected matching field)
from tableA as A
where exist
(select *(or selected matching field)
from tableB as B
where A.key = B.key)
This query will return all the rows of data that match in both tables therefore if there is a row data that is in tableA that isn't in tableB that row of data will not be retrieved.
此查询将返回两个表中匹配的所有数据行，因此如果 tableA 中的行数据不在 tableB 中，则不会检索该行数据。
回答by xkonnectDeveloper
I found it is a lot easier to use a tool like SQLMerger to do this for you. The results are displayed in a nicer way and you can go on with whatever you need to do with the data thereafter easily.
我发现使用 SQLMerger 之类的工具为您执行此操作要容易得多。结果以更好的方式显示，之后您可以轻松地继续处理您需要对数据执行的任何操作。
www.auisoft.com/SQLMerger <= the tool that makes it easy to compare data
www.auisoft.com/SQLMerger <= 便于比较数据的工具
example on comparing two tables: http://auisoft.com/SQLMerger/How-to/visualize-differences-in-2-databases/
比较两个表的示例：http: //auisoft.com/SQLMerger/How-to/visualize-differences-in-2-databases/
