SQL 比较两个表，找到缺失的行和不匹配的数据
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15926953/
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
Compare two tables, find missing rows and mismatched data
提问by greenafrican
I'd like to compare two tables and get a set of results where the lookup values are mismatched as well as where the key values are missing from the other table. The first part works fine with the following query:
我想比较两个表并获得一组结果，其中查找值不匹配以及另一个表中缺少键值。第一部分适用于以下查询：
SELECT * FROM (
SELECT mID, mLookup
FROM m) t1
FULL OUTER JOIN (
SELECT aID, aLookup
FROM a) t2
ON t1.mID = t2.aID
WHERE
t1.mID = t2.aID AND
t1.mLookup <> t2.aLookup
However, it doesn't return rows from t1 and t2 where there is no corresponding ID in the other table (because of the ON t1.mID = t2.aID).
但是，它不会从 t1 和 t2 返回其他表中没有对应 ID 的行（因为ON t1.mID = t2.aID）。
How can I achieve both in the same query?
如何在同一个查询中同时实现？
回答by Paul Williams
Remove the ID part of the WHEREclause. The FULL OUTER JOINON t1.mID = t2.aIDis enough to link the tables together. The FULL OUTER JOIN will return both tables in the join even if one does not have a match.
删除WHERE子句的 ID 部分。这FULL OUTER JOINON t1.mID = t2.aID足以将表格链接在一起。即使没有匹配项，FULL OUTER JOIN 也会返回连接中的两个表。
However, the WHERE t1.m_ID = t2.aIDclause limits the results to IDs that exist in both tables. This effectively causes the FULL OUTER JOINto act like an INNER JOIN.
但是，该WHERE t1.m_ID = t2.aID子句将结果限制为两个表中都存在的 ID。这有效地导致 的FULL OUTER JOIN行为类似于INNER JOIN。
In other words:
换句话说：
SELECT * FROM (
SELECT mID, mLookup
FROM m) t1
FULL OUTER JOIN (
SELECT aID, aLookup
FROM a) t2
ON t1.mID = t2.aID
WHERE
--t1.mID = t2.aID AND -- remove this line
t1.mLookup <> t2.aLookup
-- EDIT --
- 编辑 -
Re-reading your question, you wanted only the mismatches. In that case, you need to search on where either side's ID is NULL:
重新阅读你的问题，你只想要不匹配。在这种情况下，您需要搜索任一方的 ID 为 NULL 的位置：
SELECT * FROM (
SELECT mID, mLookup
FROM m) t1
FULL OUTER JOIN (
SELECT aID, aLookup
FROM a) t2
ON t1.mID = t2.aID
WHERE
t1.mID IS NULL OR
t2.mID IS NULL OR
t1.mLookup <> t2.aLookup
回答by Philip Kelley
The whereclause of your query filters out those rows that dont have matching "Ids". Try this:
where查询的子句过滤掉那些没有匹配“Ids”的行。尝试这个：
SELECT m.mId, m.mLookup, a.aId, a.aLookup
from m
full outer join a
on a.aId = m.mId
where m.mId is null
or a.aID is null
or m.mLookup <> a.aLookup
The full outer join gets all possible rows, and the where clause keeps all rows where one or the other side are null and, where they match (neither null), keeps only those rows where the "lookup" values differ.
全外连接获取所有可能的行，where 子句保留其中一侧或另一侧为空的所有行，并且在它们匹配（非空）的情况下，仅保留“查找”值不同的那些行。
回答by Syed
Starting from SQL Server 2008 and also valid for Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse
从 SQL Server 2008 开始，也适用于 Azure SQL 数据库、Azure SQL 数据仓库、并行数据仓库
Following is the SQL queries;
以下是 SQL 查询；
USE [test]
GO
CREATE TABLE [dbo].[Student1](
[Id] [int] NOT NULL,
[Name] [nvarchar](256) NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Student2](
[Id] [int] NOT NULL,
[Name] [nvarchar](256) NOT NULL
) ON [PRIMARY]
GO
---- You can re-run from here with your data
truncate table [Student1]
truncate table [Student2]
insert into [Student1] values (1, N'??? ????')
insert into [Student1] values (2, N'Syed Ali')
insert into [Student1] values (3, N'Misbah Arfin')
insert into [Student2] values (2, N'Syed Ali')
insert into [Student2] values (3, N'Misbah Arfin');
with StudentsAll (Id, [Name]) as
(
select s1.Id, s1.[Name] from Student1 s1
left outer join Student2 s2
on
s1.Id = s2.Id
),
StudentsMatched (Id, [Name]) as
(
select s1.Id, s1.[Name] from Student1 s1
inner join Student2 s2
on
s1.Id = s2.Id
)
select * from StudentsAll
except
select * from StudentsMatched
