Q
Problem solve Get help with specific problems with your technologies, process and projects.

WHERE EXISTS ( SELECT NULL... ) revisited

In a previous post, you wrote that for an EXISTS clause, it doesn't matter if you use SELECT 'text,' SELECT 1, or SELECT NULL. I have at some point been told that using SELECT NULL for an EXISTS should allocate less memory than selecting a value. 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.

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!

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 last published in October 2005

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close