| Subject | Re: [firebird-support] select statement with "or" or "in" clause |
|---|---|
| Author | Helen Borrie |
| Post date | 2008-06-22T22:16:44Z |
At 02:41 AM 23/06/2008, you wrote:
b) is not equivalent to a) or c) but the syntax in your example is invalid SQL (can't have ORDER BY in a subquery). A subquery is not appropriate to get this output, anyway, since you have only a simple search here. Also, presumably, you'll need NAME in the output.
select NAME, a,b,c from products
where NAME starting with 'searchstring'
order by NAME
You would use a subquery - with proper correlation syntax - if you were trying to match the id of products with an id in a different table or some kind of temporary structure, such as a GTT, where you were maintaining the search selections. In those cases, an outer join could be more efficient than a correlated subquery.
./heLen
>Firebird 2 with VB6 & ODBCa) and c) are equivalent (with a list limit of about 1000 for a). a) is simply a more compact way to write the query.
>
>Hello,
>
>my question is, whether inside a select statement the "in()" or the
>"or" clause should be used. So far I readed, that only few arguements
>for the "in()" clause is ok, but the performance will go down for many
>arguments. In my actual application a customers wish is, to filter
>products by their names and show all with this products associated
>events. That means, if there are approx 2000-3000 products and the
>filter result is 100-200 products, I have to use this 100-200 Ids for
>filtering the associated events.
>
>I have the primary index on the ID field with type BigInt, and an
>index on the field 'Name' which is the name of the product.
>
>So, actual I know three possible select statements :
>
>a) select a,b,c from products where ID in (ID1,ID2,ID3,....)
>
>b) select a,b,c from products where ID in (select ID from products
>where NAME starting with 'searchstring' order by NAME) order by ID
>
>c) select a,b,c from products where ID=xx or ID=xx or ID=xx .....
>
>Are there more (better) possibilities?
b) is not equivalent to a) or c) but the syntax in your example is invalid SQL (can't have ORDER BY in a subquery). A subquery is not appropriate to get this output, anyway, since you have only a simple search here. Also, presumably, you'll need NAME in the output.
select NAME, a,b,c from products
where NAME starting with 'searchstring'
order by NAME
You would use a subquery - with proper correlation syntax - if you were trying to match the id of products with an id in a different table or some kind of temporary structure, such as a GTT, where you were maintaining the search selections. In those cases, an outer join could be more efficient than a correlated subquery.
./heLen