如何从两个 SQL 表中获取不匹配的记录？

How to fetch unmatching records from two SQL tables?

提问by Prashant

I want to fetch the unmatching records from two table in SQL, the table structure is as follows:

我想在SQL中从两个表中获取不匹配的记录，表结构如下：

Table1

表格1

Id      Name
1       Prashant
2       Ravi
3       Gaurav
5       Naween
7       Sachin

Table2

表2

Id      Name
1       Prashant
2       Ravi
4       Alok
6       Raja

The output I want is

我想要的输出是

Id      Name
3       Gaurav
4       Alok
5       Naween
6       Raja
7       Sachin

What will be the query to fetch the required output in SQL?

在 SQL 中获取所需输出的查询是什么？

回答by brianegge

I think joeslice's answer will only give half the results. You need to union the other table. Alternatively, you could do a full outer join.

我认为 joeslice 的回答只会给出一半的结果。您需要联合另一个表。或者，您可以执行完整的外部联接。

select a.Id, a.Name from Table1 a left outer join Table2 b on a.Name = b.Name where b.Id is null
UNION ALL
select a.Id, a.Name from Table2 a left outer join Table1 b on a.Name = b.Name where b.Id is null

回答by Sam Saffron

create table #t1 (Id int, name varchar(50)) 
create table #t2 (Id int, name varchar(50)) 

insert #t1 values (1,       'Prashant')
insert #t1 values (2,      'Ravi')
insert #t1 values (3,       'Gaurav')
insert #t1 values (5,       'Naween')
insert #t1 values (7,       'Sachin')

insert #t2 values (1,       'Prashant')
insert #t2 values (2,       'Ravi')
insert #t2 values (4,       'Alok')
insert #t2 values (6,       'Raja')

select isnull(#t1.id, #t2.id), isnull(#t1.name,#t2.name)  from #t1 
full outer join #t2 on #t1.id = #t2.id
where #t2.Id is null or #t1.id is null

results:

结果：

3   Gaurav
5   Naween
7   Sachin
4   Alok
6   Raja

回答by SO User

Select Id, Name
from Table1
where Id not in (select Id from Table2)
UNION 
Select Id, Name
from Table2
where Id not in (select Id from Table1)

回答by van

You actually CAN do it with one query:

你实际上可以通过一个查询来做到这一点：

SELECT      COALESCE(table1.ID, table2.ID) AS ID,
            COALESCE(table1.Name, table2.Name) AS Name
FROM        table1
FULL JOIN   table2
        ON  table1.ID = table2.ID
WHERE       table1.ID IS NULL OR table2.ID IS NULL

will result in:

将导致：

ID          Name
----------- --------
3           Gaurav
5           Naween
7           Sachin
6           Raja
4           Alok

回答by J-16 SDiZ

SELECT * FROM 
(
  SELECT * FROM Table1
 MINUS
  SELECT * FROM Table2
)
UNION
(
  SELECT * FROM Table2
 MINUS
  SELECT * FROM Table1
)

or

或者

SELECT * FROM
 Table1 a 
FULL OUTER JOIN
 Table2 b 
ON
 a.ID=b.ID AND a.Name=b.NAME
WHERE
 a.ID IS NULL OR b.ID IS NULL

回答by TheVillageIdiot

    select t.ID, t.Name from Table1 t
    left outer join Table2 tt
    ON tt.name = t.name
    where tt.id is null

    union all

    select t.ID, t.Name from Table2 t
    left outer join Table1 tt
    ON tt.name = t.name
    where tt.id is null

回答by joeslice

select a.Id, a.Name 
from Table1 a 
left outer join Table2 b 
on a.Name = b.Name 
where b.Id is null

EDIT: My answer is wrong.... it will give you only the results from Table1 that are not found in Table2. Thanks for pointing that out.

编辑：我的答案是错误的......它只会给你表 1 中没有在表 2 中找到的结果。感谢您指出了这一点。

回答by Yaakov Ellis

Since you want to get the unmatched records from both tables, I think that you will need two queries (one for each table) which will be unioned together:

由于您想从两个表中获取不匹配的记录，我认为您需要将两个查询（每个表一个）合并在一起：

(SELECT t1.Id, t1.Name 
 FROM Table1 as t1 
 LEFT OUTER JOIN Table2 as t2 on t1.Name = t2.Name 
 WHERE t2.Id is null)
UNION
(SELECT t2.Id, t2.Name 
 FROM Table2 as t2 
 LEFT OUTER JOIN Table1 as t1 on t2.Name = t1.Name 
 WHERE t1.Id is null)

回答by shekhar hingane

SELECT COALSCE(t1.name, t2.name) AS "name"
FROM table1 t1 FULL OUTER JOIN table2 t2
ON t1.id = t2.id
WHERE t1.name IS NULL
OR t2.name IS NULL;

回答by shekhar hingane

SELECT COALESCE(t1.name, t2.name) FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.id = t2.id MINUS SELECT coalesce(t1.name, t2.name) FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id;

SELECT COALESCE(t1.name, t2.name) FROM table1 t1 FULL OUTER JOIN table2 t2 ON t1.id = t2.id MINUS SELECT coalesce(t1.name, t2.name) FROM table1 t1 INNER JOIN table2 t2 ON t1.id = t2.id;