WHERE EXISTS ( SELECT NULL... ) revisited

In How does WHERE EXISTS ( SELECT NULL... ) work? (22 February 2002), you wrote that for an EXISTS clause, it doesn't matter if you use SELECT 'text,' SELECT 1, or SELECT NULL. I have one question to this response, that I would like you to go a little deeper into.

I have at some point been told that using SELECT NULL for an EXISTS should allocate less memory than selecting a value. It seems obvious that selecting NULL would require less memory, so if the SELECT inside the EXISTS in itself could return a large number of records, that would be an argument for selecting NULL.

However, I'm not sure if SELECT NULL actually allocates memory for the SELECT result set at all, or if it just makes the comparison right away, by joining the selected data on the data from outside the EXISTS subquery. That is how I originally understood the explanation I was given about using the EXISTS clause. But by having used the statement for several purposes by now, I'm not really sure that this is the case anymore!

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

My answer needs to be understood in this context: I am not a DBA. My experience in database optimizer path selection and performance analysis is minimal. I can write all kinds of SQL, from simple to complex, to get the correct answer, but if the tables involved are really large and the performance needs additional "tweaking" (beyond the basic steps of ensuring appropriate indexes are available), then I will call a real DBA.

Consider this situation, a query to obtain a count:

select count(*) 
  from sometable
 where foo = 'bar'

Assume that the optimizer needs to allocate an integer in memory which it will use to count rows. What space must the optimizer also set aside for the actual rows that it's counting? I prefer to believe that the answer is none.

In a similar manner, what space must the optimizer set aside for the actual rows that it's searching for inside an EXISTS subquery? Again, I prefer to believe that the answer is none. I could be wrong (it wouldn't be the first time!), but even if the optimizer had to set aside some space for the subquery, it would only need to be one row's worth—so we're talking about only of a couple of bytes in total—since as soon as it finds one row, it knows the answer to the EXISTS question.

This was first published in October 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top