sql - 使用 Exists 1 或 Exists * 的子查询

Question

我曾经这样编写 EXISTS 检查:

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

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

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

这真的有区别吗？

Answer 1

不，SQL Server 很聪明，知道它正被用于 EXISTS，并且不会向系统返回任何数据。

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

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.

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

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

如果它实际上对 SELECT 列表执行某些操作，则会抛出 div by 0 错误。事实并非如此。

编辑:请注意，SQL 标准实际上讨论了这一点。

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

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>.