--- In firebird-support@yahoogroups.com, Radu Sky wrote:

> Hello,
>
> AFAIR, IN queries are not well optimized.
>
> Maybe something like this will help
>
> select * from sys_products where
> (select count(barcode) barcode from sys_barcode WHERE
> sys_barcode.barcode=sys_products.barcode)>1

I expect the next beta release to be the answer to the original
problem. But in general, I would rather recommend changing his SQL
(your query return the records he is not interested in) to

select * from sys_products sp
where not exists(select * from sys_barcode sb1
join sys_barcode sb2 on sb1.barcode = sb2.barcode
and sb1.pk < sb2.pk
where sb1.barcode = sp.barcode)

The major benefit of this is when there are many records with the same
barcode, then finding two records is sufficient to determine that this
row is not of interest. Using

select * from sys_products where
(select count(barcode) barcode from sys_barcode WHERE
sys_barcode.barcode=sys_products.barcode) <= 1

all rows are counted - and you really don't care whether that count is
2 or 5193342. Though - admittedly - on this list I find that most
people use your way of coding, so maybe it is a more commonly accepted
coding style that people find more readable (I find them similar).
Also, my suggestion would involve 10 joins if you wanted records with
ten duplicates, while you would just have to change one number using
your query.

Set