CODEWITHSUNDEEP
sql-servert-sql
kenp
kenp

Reputation: 500

Show Difference between two tables in SQL

I have two tables and i want to get the difference between them based on the name and the version of the book:

Table 1

id Name  version
1  B5077  A        
2  B5077  A     
4  B5077  B      
5  B5077  C

Table 2

id name  version
1  B5077  B        
2  B5077  C     
3  B5077  D      
4  B5077  E

SQL command (the result is really fast comparing to a full Join):

(   SELECT name, version FROM table 1 where book = 'B5077'
    EXCEPT
     SELECT name, version FROM table 2 )  
UNION ALL
(   SELECT name, version FROM table 2 where book = 'B5077'
    EXCEPT
    SELECT name, version FROM table 1)

It gives me this output:

id name  version
1  B5077 A        
2  B5077 D     
3  B5077 E

but how i can get the id of the line that has the difference and in which table ? so i can have something like this :

  id  name  version  idtable1  idtable2
   1  B5077 A        1         NULL 
   2  B5077 A        2         NULL
   3  B5077 D        NULL      3
   3  B5077 E        NULL      4

Thanks,

Upvotes: 1

Views: 290

Answers (1)

Marc Guillot
Marc Guillot

Reputation: 6455

Just select first the rows on Table 1 not present on Table 2, and then add the rows on Table 2 not present on Table 1.

select Name, Version, id as idtable1, null as idtable2
from Table1
where not exists (select * from Table2 where Table2.Name = Table1.Name and Table2.Version = Table1.version)
union
select Name, Version, null as idtable1, id as idtable2
from Table2
where not exists (select * from Table1 where Table1.Name = Table2.Name and Table1.Version = Table2.version)

Upvotes: 1

Related Questions