Sasha,

> > >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.
> >
>
> Well I guess this thread just brings on more questions, because now I

am

> confused. I always believed those 3 statements are essentially the

same

> because optimizer kicks in and ignores whatever you are trying to

select.

> Am I wrong?

Yes.

Don't confuse the optimizer function, which simply selects the best path
to follow, with an intelligent "SQL re-writer" function.


Firebird currently has no function to parse the SQL and recognize that

... where exists (select * from some_table where ..., or
... where exists (select somefield from some_table where ....

are bad and should be replaced with

... where exists (select 1 from some_table where...


There are plenty of optimizations which have yet to be tackled.


Sean