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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading