At 09:14 AM 26/03/2008, you wrote:

>I know he has his answer and this is effectively a closed thread,
>however I have a related question about it. For these exists clauses,
>is there a preferred method?
>
>1. where exists (select * from some_table where some_where_clause)

Too heavy

>2. where exists (select somefield from some_table where
>some_where_clause)

Not so heavy, but you're not trying to return a value so why use the resources?

>3. where exists (select 1 from some_table where some_where_clause)

Good.

>4. where exists (select first 1 1 from some_table where
>some_where_clause)

Bad

>
>#4 should return '1', and only 1 time (first 1).

But 3 does the same without the extra overhead on resources.

>I've always used #4 so that the exists subquery only returns one record
>and then stops. I don't know if the others return all records in the
>exists query or not.

EXISTS() *by design* returns a Boolean result as soon as the first matching row is found, regardless of what you throw at it as the output specification. The heavier the output specification the more resources have to be set up. Use syntax 3 because it's the lightest use of resources. What matters is the appropriateness of the search criteria (the WHERE clause). Have indexes available that are appropriate. For example, if a "hit" is more likely to be found nearer to the end of the possible values than to the start, use a descending index.

HOBBY HORSE: Avoid inappropriate usages of SELECT FIRST... . It's meant for extracting ordered subsets with prescribed start and finish positions and it's good for that, in the sense that DSQL doesn't provide any conventional way to do that otherwise. It's a concept best suited for file-based tables and we got it thanks to pressure from ex-MySQL users. For Fb 2 and onward, it has some handy applications for forming derived tables. But regard it always as a last resort, for when you absolutely can't get a set by some less complex (= better-performing) means.

./heLen