How to fetch unmatching records from two SQL tables?

Question

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

Table1

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

Table2

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?

Answer 1

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.

Answer 2

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.

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

Answer 3

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)

Answer 4

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

Answer 5

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

Answer 6

 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

Answer 7

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

Answer 8

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)