How to get unmatching records from two SQL tables?

Question

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

Table 1

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

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

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

Answer 1

I think the answer to joeslice will only give half the result. You need to join another table. In addition, you can perform a full external connection.

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 2

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

:

3 gaurav
5 naween
7 sachin
4 alok
6 Raja

Answer 3

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)

Answer 4

:

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

:

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

Answer 5

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

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 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

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

EDIT: .... 1, 2. , .

Answer 8

, , ( ), :

(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 9

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;

Answer 10

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