Thread: Matching a large number of user chosen

Hi,

I have an application where a user can choose a large number of distinct products to process.

The SQL that I currently use looks something like this:

select * from products where date='01012000' AND ( id='123' OR id='456' OR id='789' );

There can be maybe hundreds of different unconnected id's chosen.

Is there a more elegant/efficient way to do this than a large number of ORs ?

Thanks
JohnT

Hi John,

>
> I have an application where a user can choose a large number of distinct products to process.
>
> The SQL that I currently use looks something like this:
>
> select * from products where date='01012000' AND ( id='123' OR id='456' OR id='789' );
>
> There can be maybe hundreds of different unconnected id's chosen.
>
> Is there a more elegant/efficient way to do this than a large number of ORs ?

You could try

AND id in ('123','456','789');



--
Rob

He who dies with the most toys ...

                    ... still dies

IN should not be that much faster, as the table is scanned for each entry in
the list of id numbers.

--------------------------------------------------------------
Rasmus T. Mohr            Direct  :             +45 36 910 122
Application Developer     Mobile  :             +45 28 731 827
Netpointers Intl. ApS     Phone   :             +45 70 117 117
Vestergade 18 B           Fax     :             +45 70 115 115
1456 Copenhagen K         Email   : mailto:rmo@netpointers.com
Denmark                   Website : http://www.netpointers.com

"Remember that there are no bugs, only undocumented features."
--------------------------------------------------------------

-----Oprindelig meddelelse-----
Fra: pgsql-novice-owner@postgresql.org
[mailto:pgsql-novice-owner@postgresql.org]Pa vegne af Rob
Sendt: 12. april 2002 18:11
Til: John Taylor
Cc: pgsql-novice@postgresql.org
Emne: Re: [NOVICE] Matching a large number of user chosen


Hi John,

>
> I have an application where a user can choose a large number of distinct
products to process.
>
> The SQL that I currently use looks something like this:
>
> select * from products where date='01012000' AND ( id='123' OR id='456' OR
id='789' );
>
> There can be maybe hundreds of different unconnected id's chosen.
>
> Is there a more elegant/efficient way to do this than a large number of
ORs ?

You could try

AND id in ('123','456','789');



--
Rob

He who dies with the most toys ...

                    ... still dies


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly