CODEWITHSUNDEEP
mysqlsql
Laci K
Laci K

Reputation: 595

Mysql compare two tables and display only the difference

I would like to find the difference between two tables, because there is a column in table 2 which was linked to the table 1 ID column but sadly a site manager deleted items from table 1 and now there are a lots of unlinked rows in table 2 what causes problems on the site.

For example here are the table structures

Table 1          table 2

ID | name      ID | value (this is the ID from table 1) 
1  | one       1  | 1
2  | two       2  | 2
3  | three     3  | 4
6  | six       4  | 4
7  | seven     5  | 5
               6  | 5
               7  | 6
               8  | 7
               9  | 1
               10 | 1

As you see in table 2 some IDs from table 1 are in multiple rows and I would like to get all of them which aren't present in table 1 as the return of the query.

Just for clarification this is what I would like to get from the query

Result:

ID (form table 2) | value
 3                | 4
 4                | 4
 5                | 5
 6                | 5

I know I could use for example NOT IN but than I would have to put in about a 1000 IDs and table 1 contains much more items than what is linked in table 2

How can I do a query which will return a result like above?

Upvotes: 8

Views: 18981

Answers (3)

Naive
Naive

Reputation: 21

General query :

(select * from table1 a
    where not exists (select * from table2 b where a.id=b.id))
    
union
    
select * from table2 a
    where not exists (select * from table1 b where a.id=b.id);

NOTE: This query gives those rows of table 1 which doesn't exists in table 2 and rows of table 2 which doesn't exists in table 1

Upvotes: 0

Naive
Naive

Reputation: 21

Expected output can be achieved by :

select * from table2 b
where not exists  (select * from 

(select ID,  (case a.name
when 'one' then 1 when 'two' then 2 when 'three' then 3 
when 'six' then 6 when 'seven' then 7 else null END )AS names from 
table1 a) x

where b.value=x.names)

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93694

Use NOT EXISTS

select * 
from table2 A
Where Not exists (select 1 from table1 B Where A.ID = B.value)

Or LEFT OUTER JOIN

select *
from table2 A 
LEFT OUTER JOIN table1 B 
on A.ID = B.value
Where B.value IS NULL

Upvotes: 13

Related Questions