Hi,

> I'm trying, without success, to find how to replace an explicit IN clause

by

> a sub-select (or a join) in a "select distinct..." query without losing
> performance. I'd really appreciate any help.
> Questions at the bottom.
>
> Given, the following query :
>
> Query #1 (explicit IN):
> ==========
>
> select pres.productid
> from presence pres
> where
> ((pres.outletid in (112191, 112193, 112194, 112200, 112201, 112205))
> and (pres.valuedate >= '2003-09-01')
> and (pres.valuedate < '2003-11-01'))
>
> Query #2 (sub-select):
> ======================
>
> select pres.productid
> from presence pres inner join product prod on (pres.productid = prod.id)
> where
> ((pres.outletid in (select id from sys_idlists where groupid = 1))
> and (pres.valuedate >= '2003-09-01')
> and (pres.valuedate < '2003-11-01'))

<snip>

> How should my SQL look like to perform as fast as Q1' but without an
> explicit IN clause ?

Create for example a VIEW V_SYS_IDLISTS with :

SELECT DISTINCT
GROUP_ID,
ID
FROM
SYS_IDLISTS

and use this view in a join

SELECT
pres.productid
FROM
presence pres
join product prod on (pres.productid = prod.id)
join V_SYS_IDLISTS v on (v.ID = pres.outletid)
where
(pres.valuedate >= '2003-09-01') and (pres.valuedate < '2003-11-01')


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