CODEWITHSUNDEEP
sql
djmzfKnm
djmzfKnm

Reputation: 27195

How to fetch unmatching records from two SQL tables?

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?

Upvotes: 7

Views: 100779

Answers (12)

Jagadeesh
Jagadeesh

Reputation: 1

SELECT table1.*
FROM table1
LEFT JOIN table2 ON table1.common_key = table2.common_key
WHERE table2.common_key IS NULL

UNION ALL

SELECT table2.*
FROM table2
LEFT JOIN table1 ON table1.common_key = table2.common_key
WHERE table1.common_key IS NULL;

Upvotes: 0

Hussain
Hussain

Reputation: 308

First of all union both the tables:

with data as
(
select * from t1
union
select * from t2)
select * from data

interim temporary output:

    Id  name
1   1   Prashant
2   2   Ravi
3   3   Gaurav
4   4   Alok
5   5   Naween
6   6   Raja
7   7   Sachin

Now from data remove the common ids using inner join: THE SOLUTION

with data as
    (
    select * from t1
    union
    select * from t2)
    select * from data 
    where id not in (select t1.id from t1 inner join t2 on t1.id=t2.id);

FINAL OUTPUT:

    Id  name
1   3   Gaurav
2   4   Alok
3   5   Naween
4   6   Raja
5   7   Sachin

Upvotes: 0

shekhar hingane
shekhar hingane

Reputation: 1

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;

Upvotes: 0

shekhar hingane
shekhar hingane

Reputation: 1

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;

Upvotes: 0

van
van

Reputation: 77082

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

Upvotes: 4

Rashmi Pandit
Rashmi Pandit

Reputation: 23858

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)

Upvotes: 5

TheVillageIdiot
TheVillageIdiot

Reputation: 40537

    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

Upvotes: 2

Sam Saffron
Sam Saffron

Reputation: 131172

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

Upvotes: 5

joeslice
joeslice

Reputation: 3464

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.

Upvotes: 1

J-16 SDiZ
J-16 SDiZ

Reputation: 26930

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

Upvotes: 2

Yaakov Ellis
Yaakov Ellis

Reputation: 41550

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)

Upvotes: 1

brianegge
brianegge

Reputation: 29892

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

Upvotes: 13

Related Questions