oracle 存在/不存在：“选择 1”与“选择字段”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26461868/
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
Exists / not exists: 'select 1' vs 'select field'
提问by anonxen
Which one of the two would perform better(I was recently accused of not being careful with my code because I used the later in Oracle):
两者中的哪一个表现更好（我最近被指责不小心我的代码，因为我在 Oracle 中使用了后者）：
Select *
from Tab1
Where (not) exists(Select 1 From Tab2 Where Tab1.id = Tab2.id)
Select *
from Tab1
Where (not) exists(Select Field1 From Tab2 Where Tab1.id = Tab2.id)
Or are they both same?
还是两者都一样？
Please answer both from SQL Server perspective as well as Oracle perspective.
请从 SQL Server 的角度和 Oracle 的角度回答。
I have googled (mostly from sql-server side) and found that there is still a lot of debate over this although my present opinion/assumption is the optimiser in both the RDMBS are mature enough to understand that all that is required from the subquery is a Boolean value.
我已经用谷歌搜索（主要是从 sql-server 端）并发现对此仍有很多争论，尽管我目前的意见/假设是 RDMBS 中的优化器都足够成熟，可以理解子查询所需的所有内容是一个布尔值。
回答by Patrick Hofman
Yes, they are the same. existschecks if there is at least one row in the sub query. If so, it evaluates to true. The columns in the sub query don't matter in any way.
是的，它们是一样的。exists检查子查询中是否至少有一行。如果是，则评估为true。子查询中的列在任何情况下都无关紧要。
According to MSDN, exists:
根据MSDN，exists：
Specifies a subquery to test for the existence of rows.
指定一个子查询来测试行是否存在。
And Oracle:
和甲骨文：
An EXISTS condition tests for existence of rows in a subquery.
EXISTS 条件测试子查询中是否存在行。
Maybe the MySQL documentationis even more explaining:
也许MySQL 文档更解释：
Traditionally, an EXISTS subquery starts with SELECT *, but it could begin with SELECT 5 or SELECT column1 or anything at all. MySQL ignores the SELECT list in such a subquery, so it makes no difference.
传统上，EXISTS 子查询以 SELECT * 开头，但它可以以 SELECT 5 或 SELECT column1 或任何内容开头。MySQL 会忽略此类子查询中的 SELECT 列表，因此没有区别。
回答by TheGameiswar
I know this is old,but want to add few points i observed recently..
我知道这是旧的，但想补充几点我最近观察到的..
Even though exists checks for only existence ,when we write "select *" all ,columns will be expanded,other than this slight overhead ,there are no differences.
即使exists检查只存在，当我们写“select *” all时，列将被扩展，除了这个轻微的开销，没有区别。
Source:
http://www.sqlskills.com/blogs/conor/exists-subqueries-select-1-vs-select/
来源：http:
//www.sqlskills.com/blogs/conor/exists-subqueries-select-1-vs-select/
Update:
Article i referred seems to be not valid.Even though when we write,select 1,SQLServer will expand all the columns ..
更新：
我提到的文章似乎无效。即使在我们编写时select 1，SQLServer 也会扩展所有列..
please refer to below link for in depth analysis and performance statistics,when using various approaches..
当使用各种方法时，请参考以下链接进行深入分析和性能统计。
