Take the 2-minute tour ×

I've seen some people use EXISTS (SELECT 1 FROM ...) rather than EXISTS (SELECT id FROM ...) as an optimization--rather than looking up and returning a value, SQL Server can simply return the literal it was given.

Is SELECT(1) always faster? Would SELECTing a value from the table require work that SELECTing a literal would avoid?

share|improve this question

8 Answers 8

up vote 3 down vote accepted

For google's sake, I'll update this question with the same answer as this one (http://stackoverflow.com/questions/1597442/subquery-using-exists-1-or-exists/) since (currently) an incorrect answer is marked as accepted. Note the SQL standard actually says that EXISTS via * is identical to a constant.

No. This has been covered a bazillion times. 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


Also, don't believe me? Try running the following:

SELECT whatever
  FROM yourtable
                 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>.
share|improve this answer
thanks for the follow-up. Accepted answer changed. –  TSomKes Dec 3 '10 at 17:09

In SQL Server, it does not make a difference whether you use SELECT 1 or SELECT * within EXISTS. You are not actually returning the contents of the rows, but that rather the set determined by the WHERE clause is not-empty. Try running the query side-by-side with SET STATISTICS IO ON and you can prove that the approaches are equivalent. Personally I prefer SELECT * within EXISTS.

share|improve this answer
Agree. I just find WHERE EXISTS (SELECT * FROM ...) more readable. It's more intuitive - that you're just checking that the record exists and nothing more. WHERE EXISTS (SELECT 1 FROM ...) just seems unnatural. All a subjective opinion of course. –  njr101 Sep 22 '08 at 10:20

When you use SELECT 1, you clearly show (to whoever is reading your code later) that you are testing whether the record exists. Even if there is no performance gain (which is to be discussed), there is gain in code readability and maintainability.

share|improve this answer
This is a good point. Slightly off-topic from the question, but I agree with the idea. –  TSomKes Sep 19 '08 at 3:19

Yes, because when you select a literal it does not need to read from disk (or even from cache).

share|improve this answer
This is wrong, see: stackoverflow.com/questions/1597442/… –  Matt Rogish Oct 21 '09 at 18:24

doesn't matter what you select in an exists clause. most people do select *, then sql server automatically picks the best index

share|improve this answer

As someone pointed out sql server ignores the column selection list in EXISTS so it doesn't matter. I personally tend to use "SELECT null ..." to indicate that the value is not used at all.

share|improve this answer

If you look at the execution plan for

select COUNT(1) from master..spt_values

and look at the stream aggregate you will see that it calculates

Scalar Operator(Count(*))

So the 1 actually gets converted to *

However I have read somewhere in the "Inside SQL Server" series of books that * might incur a very slight overhead for checking column permissions. Unfortunately the book didn't go into any more detail than that as I recall.

share|improve this answer

Select 1 should be better to use in your example. Select * gets all the meta-data assoicated with the objects before runtime which adss overhead during the compliation of the query. Though you may not see differences when running both types of queries in your execution plan.

share|improve this answer

Your Answer


