MySQL Mysql选择不在表中的位置
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/354002/
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
Mysql select where not in table
提问by BCS
I have 2 tables (A and B) with the same primary keys. I want to select all row that are in A and not in B. The following works:
我有 2 个表（A 和 B）具有相同的主键。我想选择 A 中而不是 B 中的所有行。以下工作：
select * from A where not exists (select * from B where A.pk=B.pk);
however it seems quite bad (~2 sec on only 100k rows in A and 3-10k less in B)
但是它看起来很糟糕（A 中只有 100k 行约 2 秒，B 中少 3-10k）
Is there a better way to run this? Perhaps as a left join?
有没有更好的方法来运行它？也许作为左连接？
select * from A left join B on A.x=B.y where B.y is null;
On my data this seems to run slightly faster (~10%) but what about in general?
在我的数据上，这似乎运行得稍微快一些（~10%），但一般情况下呢？
采纳答案by Bill Karwin
I use queries in the format of your second example. A join is usually more scalable than a correlated subquery.
我以您的第二个示例的格式使用查询。连接通常比相关子查询更具可扩展性。
回答by Nick Berardi
I think your last statement is the best way. You can also try
我认为你的最后一句话是最好的方式。你也可以试试
SELECT A.*
from A left join B on
A.x = B.y
where B.y is null
回答by Dave Rix
I also use left joins with a "where table2.id is null" type criteria.
我还使用带有“where table2.id is null”类型条件的左连接。
Certainly seems to be more efficient than the nested query option.
当然似乎比嵌套查询选项更有效。
回答by ChoNuff
Joins are generally faster (in MySQL), but you should also consider your indexing scheme if you find that it's still moving slowly. Generally, any field setup as a foreign key (using INNODB) will already have an index set. If you're using MYISAM, make sure that any columns in the ON statement are indexed, and consider also adding any columns in the WHERE clause to the end of the index, to make it a covering index. This allows the engine to have access to all the data needed in the index, removing the need to make a second round-trip back to the original data. Keep in mind that this will impact the speed of inserts/updates/deletes, but can significantly increase the speed of the query.
联接通常更快（在 MySQL 中），但如果您发现它仍然缓慢移动，您也应该考虑您的索引方案。通常，任何设置为外键的字段（使用 INNODB）都已经有一个索引集。如果您使用 MYISAM，请确保 ON 语句中的任何列都已编入索引，并考虑将 WHERE 子句中的任何列添加到索引的末尾，使其成为覆盖索引。这允许引擎访问索引中所需的所有数据，从而无需再次返回原始数据。请记住，这会影响插入/更新/删除的速度，但可以显着提高查询速度。
回答by user3136147
This helped me a lot. Joinsare always faster than Sub Queries to give results:
这对我帮助很大。Joins总是比子查询更快给出结果：
SELECT tbl1.id FROM tbl1 t1
LEFT OUTER JOIN tbl2 t2 ON t1.id = t2.id
WHERE t1.id>=100 AND t2.id IS NULL ;
