mysql - SQL where 子句中的否定

Question

这个问题已经有答案了:

Return row only if value doesn't exist (2 个回答)

已关闭 7 年前。

嗨，我正在尝试解决否定数据的问题，我有两个表

类(class):

+----+----------+--+
| id |   name   |  |
+----+----------+--+
|  1 | 'First'  |  |
|  2 | 'Second' |  |
+----+----------+--+

列表:

+----+----------+--+
| id | id_class |  |
+----+----------+--+
|  1 |        1 |  |
+----+----------+--+

类id引用id_class

我想选择不在列表中的数据。我试过这个:

  SELECT c.name FROM classes c JOIN lists l ON (l.id_class=c.id) WHERE l.id_class!=c.id 

但是没有结果，猜测不正确 有解决方案吗？

Answer 1

您需要一个LEFT JOIN:

SELECT c.id, c.name
from classes c 
left join lists l on (l.id_class=c.id) 
where l.id_class is null

或者，您也可以使用NOT EXISTS:

select c.id, c.name
from classes c 
where not exists (select *
                  from lists l
                  where l.id_class=c.id)