使用 Exists 1 或 Exists * 的子查询 [英] Subquery using Exists 1 or Exists *

问题描述

我曾经这样写我的 EXISTS 检查:

I used to write my EXISTS checks like this:

IF EXISTS (SELECT * FROM TABLE WHERE Columns=@Filters)
BEGIN
   UPDATE TABLE SET ColumnsX=ValuesX WHERE Where Columns=@Filters
END

前世的一位 DBA 告诉我，当我执行 EXISTS 子句时，使用 SELECT 1 而不是 SELECT *

One of the DBA's in a previous life told me that when I do an EXISTS clause, use SELECT 1 instead of SELECT *

IF EXISTS (SELECT 1 FROM TABLE WHERE Columns=@Filters)
BEGIN
   UPDATE TABLE SET ColumnsX=ValuesX WHERE Columns=@Filters
END

这真的有区别吗?

推荐答案

不，SQL Server 很聪明，知道它正在用于 EXISTS，并且向系统返回 NO DATA.

No, SQL Server is smart and knows it is being used for an EXISTS, and returns NO DATA to the system.

引用微软:http://technet.microsoft.com/en-us/library/ms189259.aspx?ppud=4

子查询的选择列表几乎总是由 EXISTS 引入由星号 (*) 组成.有没有理由列出列名，因为你只是在测试行是否符合规定的条件子查询存在.

The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are just testing whether rows that meet the conditions specified in the subquery exist.

要检查自己，请尝试运行以下命令:

To check yourself, try running the following:

SELECT whatever
  FROM yourtable
 WHERE EXISTS( SELECT 1/0
                 FROM someothertable 
                WHERE a_valid_clause )

如果它实际上是在用 SELECT 列表做某事，它会抛出一个 div 零错误.没有.

If it was actually doing something with the SELECT list, it would throw a div by zero error. It doesn't.

注意，SQL 标准实际上讨论了这一点.

Note, the SQL Standard actually talks about this.

ANSI SQL 1992 标准，第 191 页 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

ANSI SQL 1992 Standard, pg 191 http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

3) 案例:
a) 如果 "*" 只包含在 中立即包含在 中，则 是等价于 那是一个任意的.

3) Case:
a) If the <select list> "*" is simply contained in a <subquery> that is immediately contained in an <exists predicate>, then the <select list> is equivalent to a <value expression> that is an arbitrary <literal>.

这篇关于使用 Exists 1 或 Exists * 的子查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！