--- In firebird-support@yahoogroups.com, Daniel Rail <daniel@a...>
wrote:

>
> It is as designed. Because the second WHERE clause is transformed
> internally to: (e.id_visite=8) or (e.id_visite=10) or

(e.id_visite=10546)

> Which will use an index. And, with a subquery, it's hard to use an
> index in the same manner.
>
> But, if I'm not mistaken the IN clause with a subquery should be be
> transformed to:
>
> where exists(select 1 FROM Patient
> left join Visite on (Patient.ID_Patient = Visite.ID_Patient)
> WHERE (Patient.ID_Patient = '45691')
> (Patient.id_visite=e.id_visite))
>
> So what happens if you change the first WHERE clause to what I've
> written above? Do you have an index on Patient.id_visite?
>

There is no field patient.id_visite. But if there was one, I'd have
the same result ... examen would be scanned completely... I realise I
will have no other choice and have to do this in Delphi instead of
doing it with one SQL...

Thank you

Evelyne