实践“存在（从...中选择 1）”在哪里？来自？
绝大多数人支持我自己的观点，即两者之间没有区别以下陈述：
SELECT * FROM tableA WHERE EXISTS (SELECT * FROM tableB WHERE tableA.x = tableB.y)
SELECT * FROM tableA WHERE EXISTS (SELECT y FROM tableB WHERE tableA.x = tableB.y)
SELECT * FROM tableA WHERE EXISTS (SELECT 1 FROM tableB WHERE tableA.x = tableB.y)
SELECT * FROM tableA WHERE EXISTS (SELECT NULL FROM tableB WHERE tableA.x = tableB.y)
然而今天，在我们的内部开发人员会议上，我遇到了相反的说法，提倡 select 1 是正确的方法，而 select *选择所有（不必要的）数据，从而损害性能。
我似乎记得有一些旧版本的 Oracle 或其他版本，但我找不到对此的引用。所以，我很好奇 - 这种做法是如何诞生的？这个神话从何而来？
添加：因为有些人坚持认为有证据表明这确实是一个错误的信念，这里 - a google 查询 显示了很多人们这么说。如果你太懒了，请检查这个直接链接，其中有人甚至比较执行计划以发现它们是等效的。
The overwhelming majority of people support my own view that there is no difference between the following statements:
SELECT * FROM tableA WHERE EXISTS (SELECT * FROM tableB WHERE tableA.x = tableB.y)
SELECT * FROM tableA WHERE EXISTS (SELECT y FROM tableB WHERE tableA.x = tableB.y)
SELECT * FROM tableA WHERE EXISTS (SELECT 1 FROM tableB WHERE tableA.x = tableB.y)
SELECT * FROM tableA WHERE EXISTS (SELECT NULL FROM tableB WHERE tableA.x = tableB.y)
Yet today I came face-to-face with the opposite claim when in our internal developer meeting it was advocated that select 1 is the way to go and select * selects all the (unnecessary) data, hence hurting performance.
I seem to remember that there was some old version of Oracle or something where this was true, but I cannot find references to that. So, I'm curious - how was this practice born? Where did this myth originate from?
Added: Since some people insist on having evidence that this is indeed a false belief, here - a google query which shows plenty of people saying it so. If you're too lazy, check this direct link where one guy even compares execution plans to find that they are equivalent.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
你的问题的主要部分是——“这个神话是从哪里来的？”
因此，为了回答这个问题，我猜想人们通过 sql 学到的第一个性能提示是
select *在大多数情况下效率很低。事实上，在这种特定情况下，它并不是效率低下，因此有些违反直觉。因此，人们对此表示怀疑也就不足为奇了。但一些简单的研究或实验应该足以消除大多数神话。尽管人类历史表明神话很难消除。The main part of your question is - "where did this myth come from?"
So to answer that, I guess one of the first performance hints people learn with sql is that
select *is inefficient in most situations. The fact that it isn't inefficient in this specific situation is hence somewhat counter intuitive. So its not surprising that people are skeptical about it. But some simple research or experiments should be enough to banish most myths. Although human history kinda shows that myths are quite hard to banish.作为演示，尝试这些
现在阅读 ANSI 标准。 ANSI-92，第 191 页，案例 3a
最后，大多数 RDBMS 上的行为应忽略 EXISTS 子句中的 *。根据昨天的这个问题（ Sql Server 2005 - 如果不存在则插入 ）这在 SQL Server 2000 上不起作用，但我知道它在 SQL Server 2005+ 上起作用
As a demo, try these
Now read the ANSI standard. ANSI-92, page 191, case 3a
Finally, the behaviour on most RDBMS should ignore THE * in the EXISTS clause. As per this question yesterday ( Sql Server 2005 - Insert if not exists ) this doesn't work on SQL Server 2000 but I know it does on SQL Server 2005+
对于 SQL Server 来自 Conor Cunningham查询优化器团队解释了他通常使用
SELECT 1的原因编辑：但是，自从发布此答案以来，我对此进行了一些详细研究，并得出结论：
SELECT 1确实不会避免此列扩展。 完整详情请参见此处。For SQL Server Conor Cunningham from the Query Optimiser team explains why he typically uses
SELECT 1Edit: However I have looked at this in some detail since posting this answer and come to the conclusion that
SELECT 1does not avoid this column expansion. Full details here.这个问题有一个答案，说是 MS Access 的某个版本实际上没有忽略 SELECT 子句的字段。我做过一些 Access 开发，并且听说
SELECT 1是最佳实践，因此在我看来这很可能是“神话”的根源。SQL EXISTS 使用变体的性能
This question has an answer that says it was some version of MS Access that actually did not ignore the field of the
SELECTclause. I have done some Access development, and I have heard thatSELECT 1is best practice, so this seems very likely to me to be the source of the "myth."Performance of SQL EXISTS usage variants