mysql - 如何在MySQL中查找其他列中不存在的列的值

Question

我有两个表，它们应该在 mId 和 keyValue 列中存储相同的值。但是我运行以下命令并得到以下结果:

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


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

找到Type表中刚好存在的mId。我运行以下命令并得到以下结果:

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)

问题是什么？

Answer 1

我认为你可以通过这种方式找到缺失值

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

你也可以试试不在

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