If Beneficiarios.Persona_id is unique, then you're right in that a
join could be the solution here. If it is not, whereas Personas.
Persona_id is unique, then a SELECT DISTINCT would be the solution. If
there are duplicates in both tables when you only want to show
duplicates from the Personas table, then you're either stuck with
using IN or EXISTS, possibly rewriting your query like:

select Personas.Apellido, Personas.Nombre from Personas
INNER JOIN Beneficiarios a ON
Personas.Persona_id = Beneficiarios.Persona_id
WHERE Beneficiarios.Beneficiario_Titular_Id = :Titular_ID
AND NOT EXISTS(SELECT * FROM Beneficiarios b
where b.Persona_id = a.Personas_id
AND b.Beneficiario_Titular_Id = a.Beneficiario_Titular_Id
AND b.Beneficiarios_id < a.Beneficiarios_id)

(I don't know enough to tell whether this will be slower or faster
than just using IN or EXISTS without any JOIN)

I don't agree in there being a general problem with using IN or
EXISTS, one just has to remember that this normally will be done for
every possible record returned. Hence, it is often slow if that is
your only limiting criterium on a big table, whereas it is brilliant
when you have additional (indexed) criteria limiting the result set.

Set

--- In firebird-support@yahoogroups.com, Alexander Gräf wrote:
> > Is there a way to make the engine work with "in" clause like a
> > join?
>
> If you want a join, why not simply use a join. These "IN" and
> "EXISTS" statements proved several times to be too slow. They are
> simply fast when you don't retrieve too many records, or if they are
> static.
>
> Why not try:
>
> select Personas.Apellido, Personas.Nombre from Personas
> INNER JOIN Beneficiarios ON
> (Personas.Persona_id = Beneficiarios.Persona_id
> and Beneficiarios.Beneficiario_Titular_Id = :Titular_ID)
>
> or (I dont know what yields the result you are searching for)
>
> select Personas.Apellido, Personas.Nombre from Personas
> INNER JOIN Beneficiarios ON
> Personas.Persona_id = Beneficiarios.Persona_id
> WHERE Beneficiarios.Beneficiario_Titular_Id = :Titular_ID