MySQL NOT IN return a wrong number

Question

I have to clean a database with a lot of orphaned entries, on this case i have a table 3 tables

'Email' (69529 entries)
'ServiceHasEmail' (5782 entries)
'UserHasEmail' (26254 entries)

The two last tables reference the 'Email' table, so this table should have 26254 + 5782 (32036) entries.

I built a query to select all the entries on the 'Email' table which are not referenced on the UserHasEmail and ServiceHasEmail tables:

SELECT * FROM Email e 
WHERE e.EML_Id NOT IN (SELECT EML_Id FROM ServiceHasEmail) 
   AND e.EML_Id NOT IN (SELECT EML_Id FROM UserHasEmail)

But this query returns me 40383 entries in place of 37493 (69529 - (26254 + 5782))

What i am missing here?

Are you sure some email addresses don't appear in both ServiceHasEmail and UserHasEmail? That would result in more rows than your expected amount. — Michael Berkowski
– Michael Berkowski, Commented Jan 19, 2012 at 13:51
I guess 2890 EML_id are in ServiceHasEmail AND in UserHasEmail: Try SELECT count(*) FROM ServiceHasEmail INNER JOIN UserHasEmail ON ServiceHasEmail.EML_Id=UserHasEmail.EML_Id — Eugen Rieck
– Eugen Rieck, Commented Jan 19, 2012 at 13:51

Florin Ghita · Accepted Answer · 2012-01-19 13:50:43Z

1

This can be because

tables ServiceHasEmail and UserHasEmail contains some emails both.
tables ServiceHasEmail and UserHasEmail contains duplicates.

You can verify:

select count(distinct email) from Email 

select count(distinct email) from ServiceHasEmail

select count(distinct email) from UserHasEmail

and

select count(distinct Email) 
from
(select Email from ServiceHasEmail
 union all
 select Email from UserHasEmail
)

And your query should be

SELECT count(distinct Email) 
FROM Email e 
WHERE e.EML_Id NOT IN (SELECT EML_Id FROM ServiceHasEmail) 
   AND e.EML_Id NOT IN (SELECT EML_Id FROM UserHasEmail)

answered Jan 19, 2012 at 13:50

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JNK Over a year ago

It could also be due to NULL values, since NULL won't evaluate TRUE when compared to a NOT IN.

DoesNotCompute Over a year ago

Thanks! for some reasons, some EML_Id indexes were duplicated in the ServiceHasEmail table although they shouldn't be. Looks like it will be more complicated than i expected to clean this database...

xpapad · Accepted Answer · 2012-01-19 13:51:22Z

0

Looks like some ServiceHasEmail and UserHasEmail reference the same Email.

answered Jan 19, 2012 at 13:51

xpapad

4,4761 gold badge26 silver badges26 bronze badges

Comments

DerVO · Accepted Answer · 2012-01-19 13:52:35Z

0

You could have EML_Id's, that are present both in ServiceHasEmail and UserHasEmail.

I suppose you have exactely 2,890 of them. Please try

SELECT * FROM `ServiceHasEmail` INNER JOIN `UserHasEmail` USING(`EML_Id`)

to verify this.

answered Jan 19, 2012 at 13:52

DerVO

3,6791 gold badge27 silver badges27 bronze badges

Comments

Mateus · Accepted Answer · 2012-01-19 13:59:00Z

0

you can use the following query:

SELECT * FROM Email e WHERE e.EML_Id NOT IN (SELECT EML_Id FROM ServiceHasEmail UNION SELECT EML_Id FROM UserHasEmail)

answered Jan 19, 2012 at 13:59

Mateus

1

Collectives™ on Stack Overflow

MySQL NOT IN return a wrong number

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related