How find the not exist values of a column in other column in MySQL

Question

I have two tables which should store the same value in the mId and keyValue columns. But I run the following command and I got the following results:

mysql> select count(distinct mId) from Types;
+---------------------+
| count(distinct mId) |
+---------------------+
|             2098350 |
+---------------------+


select count(distinct keyValue) from EntityIndex;
 +--------------------------+
 | count(distinct keyValue) |
 +--------------------------+
 |                  2095481 |
 +--------------------------+

To find the mId which are just exist in Type table. I run the following commands and I got the following results:

mysql> select distinct mId from Types where not exists 
   (select distinct keyValue from EntityIndex);
Empty set (0.00 sec)


mysql> select distinct keyValue from EntityIndex where not exists 
(select distinct mId from Types);
Empty set (0.01 sec)

what is the problem?

Answer 1

I think you can find the missing values this way

select a.mId, b.keyValue 
from Types as a
left join EntityIndex as b on a.mId = b.KeyValue 
where b.kvalue is null 

And you can try also not in

 select distinct mId from Types where mId not in
    (select distinct keyValue from EntityIndex);