在SQL中，为什么是“Distinct”？当查找某些“未显示”的项目时，未在子查询中使用在另一张桌子上？
通常，当查找某些未在其他表中显示的项目时，我们可以使用：
select * from gifts where giftID not in (select giftID from sentgifts);
或在
select * from gifts where giftID not in (select distinct giftID from sentgifts);
第二行添加“distinct”，这样结果表会更小，并且可能也让搜索“not in”更快。
那么，使用“distinct”不是可取的吗？通常，在这种情况下，我不会看到它在子查询中使用。使用它有好处还是坏处？谢谢。
Usually when looking for some items not showing up in the other table, we can use:
select * from gifts where giftID not in (select giftID from sentgifts);
or
select * from gifts where giftID not in (select distinct giftID from sentgifts);
the second line is with "distinct" added, so that the resulting table is smaller, and probably let the search for "not in" faster too.
So, won't using "distinct" be desirable? Often than not, I don't see it being used in the subquery in such a case. Is there advantage or disadvantage of using it? thanks.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
这些查询的结果将始终相同。
MySQL 的优化器很清楚这一点，并将对两个查询使用相同的计划。
如果
sentgifts.giftID已建立索引，查询将优化为使用index_subquery访问路径：它将搜索索引并在索引上返回TRUE第一次索引命中时未命中或FALSE。无论您是否使用
DISTINCT，这都是相同的。The result of these queries will always be the same.
MySQL's optimizer is well aware of that and will use the same plan for both queries.If
sentgifts.giftIDis indexed, the query will be optimized to use theindex_subqueryaccess path: it will search the index and returnTRUEon index miss orFALSEon first index hit.This will be same whether you use
DISTINCTor not.当您对结果调用 DISTINCT 时，需要扫描列表才能找到并删除重复项。这是一个缓慢的操作，如果没有它，整个查询很可能会更快。
When you call
DISTINCTon a result, it required a scan through the list in order to find and remove the duplicated. This is a slow operation, and there is a good chance that the query as a whole will be faster without it.我认为，您也可以用上面的样式编写相同的查询。
只是，您必须找到哪一个更适合您（在性能或其他标准方面）。
编辑：这是 页面说，最好使用
NOT IN或LEFT JOIN。希望有帮助。
注意：我没有任何关于
mysql的经验I think, you can write the same query in the above style as well.
Its just that, you will have to find which one works better for you (in terms of performance or other criteria).
EDIT: Here is the page which says, it is better to use
NOT INorLEFT JOIN.Hope that helps.
Note: I don't have any experience on
mysql