从表B中不存在的表A中选择 [英] Select from table A which does not exist in table B

问题描述

我正在尝试为MySQL编写SELECT语句，该语句从表A中选择表B中不存在的内容.例如:

I am trying to compose a SELECT statement for MySQL which select from table A what does not exist in table B. For example:

表A:

+------+
| BAND |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
+------+

表B:

+------+
| HATE |
+------+
| 1    |
| 5    |
+------+

因此，如果表A是所有乐队，而表B是我讨厌的乐队，那么我只希望我不讨厌的乐队.因此，选择的结果应为:

So if table A is all bands, and table B is the bands I hate, then I only want bands I do NOT hate. So the result of a select should be:

+------+
| BAND |
+------+
| 2    |
| 3    |
| 4    |
+------+

我将如何为此编写一个选择?这是我最后的尝试:

How would I write a single select for this? Here was my last attempt:

SELECT * FROM A LEFT JOIN B ON A.BAND = B.HATE WHERE B.HATE IS NULL;

上面的行已修复！参见下面的注释..."= NULL"与"IS NULL".

The line above has been fixed! See comments below..."= NULL" versus "IS NULL".

推荐答案

我会使用联接

select A.*
from A left join B on A.BAND = B.HATE
where B.HATE IS NULL;

记住:为您的表创建适当的索引

Remember: Create the appropriate indexes for your table

这篇关于从表B中不存在的表A中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！