Hi Tanmoy!

Put the values in a table (it might be a normal or a temporary table, you know best which will be suitable), and then use the EXISTS with subselect that I wrote below. EXISTS are normally quick, though, of course, Field3 must be indexed in your (temporary) table.

HTH,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Tanmoy Ghoshal
Sent: 16. november 2010 10:44
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Doubts about exists predicate

Hi Set,

Thank you for reply.

I have more than 1500 constant values. Here IN not accept more than 1500, then
what is the best way to write SQL to get data in less time.

I need to write SQL which should not take much time to execute.

As a beginner, If possible Please help me.

Thanks once again.

Tanmoy.

________________________________
Set wrote:

IN is good when using constants, EXISTS when involving a subselect. I.e. I would
use


Select Field1, Field2 from Table1 where Field3 IN ('Value1', 'Value2',
'Value3');

whereas I would replace

Select t1.Field1, t1.Field2 from Table1 t1 where t1.Field3 IN (select t2.Field3
from Table2 T2);

with

Select t1.Field1, t1.Field2 from Table1 t1 where exists(select * from Table2 T2
where t1.Field3 = t2.Field3);

To be precise (normally it is not a practical issue, it is only relevant when
dealing with NULL), IN (<subselect>) can always be replaced by EXISTS, whereas
NOT IN (<subselect>) isn't always identical to NOT EXISTS.

HTH,
Set