MySQL 从表A中选择表B中不存在的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15890367/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Select from table A which does not exist in table B
提问by TSG
I am trying to compose a SELECT statement for MySQL which select from table A what does not exist in table B. For example:
我正在尝试为 MySQL 编写一个 SELECT 语句，它从表 A 中选择表 B 中不存在的内容。例如：
Table A:
表一：
+------+
| BAND |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
Table B:
表 B：
+------+
| HATE |
+------+
| 1 |
| 5 |
+------+
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:
因此，如果表 A 是所有乐队，而表 B 是我讨厌的乐队，那么我只想要我不讨厌的乐队。所以选择的结果应该是：
+------+
| 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;
EDIT: The line above has been fixed! See comments below..."= NULL" versus "IS NULL".
编辑：上面的行已修复！请参阅下面的评论...“= NULL”与“IS NULL”。
回答by Barranka
I would use a join
我会使用连接
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
记住：为你的表创建合适的索引
回答by Stephen Fischer
You can use IN, but it's super inefficient:
您可以使用IN，但它的效率非常低：
SELECT * FROM tableA WHERE id NOT IN (SELECT id FROM tableB)
回答by Hyman
SELECT * FROM tableA WHERE id NOT EXISTS (SELECT DISTINCT id FROM tableB)
or
或者
SELECT * FROM tableA WHERE id NOT EXISTS (SELECT id FROM tableB GROUP BY id)
回答by user3021515
SELECT BAND FROM A WHERE BAND NOT EXISTS(SELECT DISTINCT HATE FROM B)
OR
或者
SELECT BAND FROM A WHERE NOT EXISTS ( SELECT HATE FROM B WHERE A.BAND = B.HATE);
