0

I've been told that when checking for the existence of a row in a table, this will work:

EXISTS(SELECT 1 FROM....)

but that a "better" way in regards to speed performance of the query would be to use this:

EXISTS(SELECT TOP(1) NULL FROM......)

I've searched online for anything that references this comparison and the only information I can find says that this is a personal preference and there actually is no performance gain of one over the other. There's always the "case by case" aspect of a question like this, but in general, is there any performance gain from using the second implementation of EXISTS() over the first one?

11
  • 2
    TOP 1 and DISTINCT are discarded by the optimizer when you use EXISTS or NOT EXISTS anyway. You should probably exact revenge on whomever told you that. Mar 7 at 18:28
  • 2
    Inside an exists you can even use select * with zero impact. It only checks for the existence of rows. The columns in the query are discarded. You can even do crazy stuff like "exists(select 1/0 from..." and it works just fine. :P
    – Sean Lange
    Mar 7 at 20:38
  • 1
    @Charlieface In your example you use id/0 and not 1/0 that is different, try tu use what Sean Lange said, 1/0 and you'll get no error
    – sepupic
    Mar 8 at 9:18
  • 1
    @SeanLange I know why it happens, I was just saying it's not a blanket statement that exists (select 1/0 always works. Mar 8 at 15:28
  • 1
    @Charlieface true...as with anything sql server the only truly blanket statement is "it depends". LOL
    – Sean Lange
    Mar 8 at 22:02

1 Answer 1

3

To be sure, you would have to check the execution plans.

But it's almost certain, given how trivial an optimization it is, that the compiler will always discard the following in the top level of an EXISTS, as they do not affect the semantics:

  • All SELECT columns
  • DISTINCT
  • TOP
  • ORDER BY

All you are doing is looking to see if there is any row, and none of these constructs can reduce it to zero.

Note that this isn't always true: there are some constructs that will interact and preserve some of those features because of their semantics. For example:

  • OFFSET FETCH
  • INTERSECT/EXCEPT (but not UNION)
  • Column-level permissions, if they exist on that table, mean you need to select only from a column that you have permission on, rather than SELECT NULL.

Common practice is to use either SELECT 1 or SELECT NULL.

For clarity, I always write an EXISTS with the SELECT on the same line and the FROM on the next, showing that the SELECT is really just syntax, like this:

WHERE EXISTS (SELECT 1
    FROM SomeTable

In some DBMSs, you can even leave out the columns completely

WHERE EXISTS (SELECT
    FROM SomeTable
1
  • Thank you so much for reading my question and providing such a clearly explained and thorough answer! I will take this information and do some in depth testing myself, I asked this question because I had done some initial research, as my question stated, and was only able to come up with references that said there is no difference but didn't provide any substance to back that up. Upvoted and marked as accepted answer. THANK YOU! Mar 8 at 19:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.