CODEWITHSUNDEEP
mysqlsqljoinmatch
sediq khan
sediq khan

Reputation: 545

Finding Non Matching ID

I have two tables, called "One" and "Two", they are both the same, table "One" has some IDs that are not there in Table "Two". but when i run this mysql Query, i can not get those non-matching values.

select count(bene) from one where bene not in (select bene from two);

And the result is Zero. so how do i get the non-matching values from Table one that are not there in table Two?

Upvotes: 1

Views: 325

Answers (2)

dotnetom
dotnetom

Reputation: 24901

You can perform a left join and find only values that do not exist in second table. Below query is assuming that bene is your ID column:

SELECT one.*
FROM one 
    LEFT JOIN two ON one.bene = two.bene
WHERE two.bene IS NULL

or if you need just the count

SELECT COUNT(one.bene)
FROM one 
    LEFT JOIN two ON one.bene = two.bene
WHERE two.bene IS NULL

When performing a Left join you find all values in table one with corresponding values in table two. If there is no match all table two values will be NULL.

Then as a second step you use WHERE to filter only rows that have NULL for table two. This way you find all records in table one that do not have matching records in table two.

Upvotes: 1

gu mingfeng
gu mingfeng

Reputation: 1050

select count(*) from one a where not exists (select * from two b where a.bene=b.bene)

Upvotes: 0

Related Questions