SQL 为什么有一个 SELECT 1 from table？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7633086/
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
Why is there a SELECT 1 from table?
提问by Oh Chin Boon
I came across a fictitious SQL, i am not sure what is the original intend, it looks like:
我遇到了一个虚构的 SQL，我不确定最初的意图是什么，它看起来像：
SELECT COUNT (*)
INTO miss
FROM billing b
WHERE b.network= network1
and NOT EXISTS (SELECT 1 from vas NV WHERE NV.network =
b.network);
Why is there a select 1, and not exists?
为什么有一个选择 1，而不存在？
采纳答案by Benoit
When using the EXISTSkeyword you need to have a sub-select statement, and only the existence of a row is checked, the contents of the row do not matter. SELECTis a keyword that controls what is inside the columnsthat are returned. SELECTing 1or NV.networkwill return the same number of rows.
使用EXISTS关键字时需要有一个子选择语句，并且只检查一行是否存在，行的内容无关紧要。SELECT是一个关键字，用于控制返回的列中的内容。SELECTing 1orNV.network将返回相同数量的行。
Therefore you can SELECT whatever you want, and canonical ways to do that include SELECT NULLor SELECT 1.
因此，您可以选择任何您想要的东西，而规范的方法包括SELECT NULL或SELECT 1。
Note that an alternative to your query is:
请注意，您的查询的替代方法是：
SELECT count(*) INTO miss
FROM billing b
LEFT JOIN vas NV ON NV.network = b.network
WHERE b.network = network1
AND NV.network IS NULL
(left join fills right-hand columns with NULLvalues when the ONcondition cannot be matched.
（NULL当ON条件无法匹配时，左连接用值填充右侧列。
回答by duffymo
SELECT 1 from vas NV WHERE NV.network = b.network
If this query returns a row, it means that there is a record in the NV table that matches one in the billing table.
如果此查询返回一行，则表示 NV 表中存在与计费表中的记录相匹配的记录。
NOT EXISTS negates it, satisfying there WHERE clause if there is NOT a record in the NV table that matches one in the billing table.
NOT EXISTS 否定它，如果 NV 表中没有与计费表中的记录匹配的记录，则满足 WHERE 子句。
回答by Ollie
There is a great AskTom Q&A on the use of EXISTS vs IN (or NOT EXISTS vs NOT IN):
关于 EXISTS 与 IN（或 NOT EXISTS 与 NOT IN）的使用，AskTom 有一个很棒的问答：
http://asktom.oracle.com/pls/asktom/f?p=100:11:1371782877074057::::P11_QUESTION_ID:953229842074
http://asktom.oracle.com/pls/asktom/f?p=100:11:1371782877074057::::P11_QUESTION_ID:953229842074
Basically using EXISTS only checks for the existence of the row in the subselect and does not check every matching row (which IN would). Therefore when using EXISTS or NOT EXISTS you do not need to actually select a particular value so selecting a placeholder (in this case "1") is enough.
基本上使用 EXISTS 只检查子选择中行的存在，而不检查每个匹配的行（IN 会）。因此，在使用 EXISTS 或 NOT EXISTS 时，您不需要实际选择特定值，因此选择占位符（在本例中为“1”）就足够了。
In your particular SQL statement, the NOT EXISTS clause ensures that the main SELECT will only return rows where there isn't a corresponding row in the VAS table.
在您的特定 SQL 语句中，NOT EXISTS 子句确保主 SELECT 仅返回 VAS 表中没有相应行的行。
回答by Naveen Babu
This code of yours is for mainly written from a performance stand point
您的这段代码主要是从性能角度编写的
I am mentioning only about the inner query. Since it needed explanation for user. What ever sql I have used should be inserted to the actual query user has used above
我只提到内部查询。因为它需要向用户解释。我使用过的任何 sql 都应该插入到用户在上面使用的实际查询中
and NOT EXISTS (SELECT 1 from vas NV WHERE NV.network =
b.network);
Explaining the inner query onlyUsually people use to put select NV.networdbut this return a data that is just used to identify if there doesn't exist a data. So, ideally what ever is returned in inner query is not even checked in the parent query. So to reduce Bytes in explain plan, we use select 1which will have minimum byte cost and which in-turn will reduce the cost of the query.
仅解释内部查询通常人们select NV.netword习惯于放置，但这会返回一个仅用于识别数据是否不存在的数据。因此，理想情况下，在内部查询中返回的内容甚至不会在父查询中进行检查。因此，为了减少解释计划中的字节数，我们使用select 1哪个字节成本最低，哪个反过来会降低查询成本。
To view the difference i suggest downloading oracle sql developerand running both the query in explain plan window and watch out the bytes column for each query
要查看差异，我建议下载oracle sql developer并在解释计划窗口中运行查询并注意每个查询的字节列
SELECT nv.network from vas NV WHERE NV.network = b.network
// cost will be depended on the value nv.network contain and that is selected in the where condition
while
尽管
SELECT 1 from vas NV WHERE NV.network = b.network
// cost will be independent of the column and cost lesser bytes selected and lesser cost.
Not exist you will be able to check with other answers.
不存在，您将能够检查其他答案。
