Mysql compares two tables and only displays the difference

I would like to find the difference between the two tables because there is a column in table 2 that was related to the id column of table 1, but unfortunately the site manager was removing items from table 1 and now there are many unrelated rows in table 2, which is causes problems on the site.

For example 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 can see from Table 2, some of the IDs from Table 1 are on multiple rows, and I would like to get all of them that are not presented in Table 1 as a query return.

Just to clarify this is what I would like to get from the request

Result:

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

      

I know that I could use for example NOT IN, but I would have to enter about 1000 IDs and table 1 contains many more items than what is related in table 2

How can I execute a query that will return the result as above?

+3


source to share


1 answer


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

      

+6


source







All Articles