> 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)
> 2. where exists (select somefield from some_table where
> some_where_clause)
> 3. where exists (select 1 from some_table where some_where_clause)
> 4. where exists (select first 1 1 from some_table where
> some_where_clause)
>
> #4 should return '1', and only 1 time (first 1).
>
> I've always used #4 so that the exists subquery only returns one

record

> and then stops.

Actually, #4 is the least desirable/slowest of the methods listed!!


SELECT FIRST 1 ...

Does *not* find the first record and stop!

It builds a dataset of *all* records meeting the criteria and then
returns the first row of the set.


Regarding the EXISTS syntax options, although I suspect they are
more-or-less equivalent, I have always used #3:

WHERE EXISTS (select 1 from some_table where some_where_clause)

For me, the "SELECT 1 FROM" visually 'reinforces' the fact that it is an
EXISTS clause.


Sean