Originally posted by Joachim Rohde:
Thanks for your answer Paul. Your query is ca. 10% faster. In the meanwhile I talked to the responsible person and I was told it should not be a problem to set an index.
I've got a question though: what exactly does "select 1" means in your query?
Hi Joachim,
Well... there are two answers here. The short answer is the sub-query returns a value of 1 for each row that is true... you could really use anything you like... the general practice is to "Select 1" with an Exists clause.
The more important concept is that the EXISTS clause is a boolean True/False for the sub-query (you can also use NOT EXISTS in a sub-query). The sub-query will evaluate to TRUE if any rows are returned for the sub-query (and TRUE if no rows are returned for NOT EXISTS).
Using a correlated sub-query (a query that has reference to the outer query) is usually faster when you use it in this manner than a non-correlated sub-query. A non-correlated sub-query must also perform a sort when the comparison operator is IN, NOT IN, = ANY, <> ANY, = ALL, or <> ALL.
I can clarify if I rushed through this too quickly... its a nice diversion from what I'm working on right now.
[ November 15, 2008: Message edited by: Paul Campbell ]