Hi,

> Can you tell me why when we use "IN" instead of " = " in a sub-select
> it returns all the rows and not only matching rows ???
>
> Exemple :
>
> SELECT
>
> NUMVOL, DATEVOL, NUMAVION, IDPILOTE
> from affectation
> where IDPILOTE = (select first :n skip (:n * :p - :n) IDPILOTE from
> pilote)
>
> It works !!! returns 3 rows.
>
> SELECT
>
> NUMVOL, DATEVOL, NUMAVION, IDPILOTE
> from affectation
> where IDPILOTE IN (select first :n skip (:n * :p - :n) IDPILOTE from
> pilote).
>
> Don't work returns all the rows...

Currently the IN-predicate is internally exactly the same as a EXISTS.
That meant you can rewrite a IN :

SELECT * FROM TableA
WHERE
TableA.FieldA IN
(SELECT FIRST 1 SubTable.SubField FROM SubTable)

to an EXISTS to see why this happens :

SELECT * FROM TableA
WHERE
EXISTS(SELECT FIRST 1 SubTable.SubField
FROM SubTable WHERE SubTable.SubField = TableA.FieldA)

For every row in TableA the EXISTS with sub-select is executed.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81