Subquery using Exists 1 or Exists *

by

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

Quoth Microsoft:
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.

To check yourself, try running the following:

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

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

EDIT: Note, the SQL Standard actually talks about this.

ANSI SQL 1992 Standard, pg 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>.

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. SQL where clause odd behavior [closed]
  4. How do I obtain a Query Execution Plan in SQL Server?
  5. How do I escape a single quote in SQL Server?
  6. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  7. Only inserting a row if it’s not already there
  8. Unpivot with column name
  9. Selecting data from two different servers in SQL Server
  10. How important is the order of columns in indexes?
  11. Remove Identity from a column in a table
  12. Why no windowed functions in where clauses?
  13. Formatting Numbers by padding with leading zeros in SQL Server
  14. T-SQL Cast versus Convert
  15. What is a stored procedure?
  16. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  17. convert Excel Date Serial Number to Regular Date
  18. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  19. Partition Function COUNT() OVER possible using DISTINCT
  20. Need to list all triggers in SQL Server database with table name and table’s schema
  21. Count based on condition in SQL Server
  22. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  23. Best equivalent for IsInteger in SQL Server
  24. Syntax of for-loop in SQL Server
  25. How to correctly insert newline in nvarchar [duplicate]
  26. How do you copy a record in a SQL table but swap out the unique id of the new row?
  27. Using IF ELSE statement based on Count to execute different Insert statements
  28. Cumulative Total in MS Sql server [duplicate]
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment