Re: OR vs UNION

Scott,

> I have a query that uses a series of ORs and I have heard that sometimes
> this type of query can be rewritten to use UNION instead and be more
> efficient.

I'd be interested to know where you heard that; as far as I know, it could
only apply to conditional left outer joins.

> select distinct f.name,fl.fmin,fl.fmax,fl.strand,f.type_id,f.feature_id
> from feature f, featureloc fl
> where
> (f.type_id = 219 OR
> f.type_id = 368 OR
> f.type_id = 514 OR
> f.type_id = 475 OR
> f.type_id = 426 OR
> f.type_id = 456 OR
> f.type_id = 461 OR
> f.type_id = 553 OR
> f.type_id = 89) and
> fl.srcfeature_id = 1 and
> f.feature_id = fl.feature_id and
> fl.fmin <= 2491413 and fl.fmax >= 2485521

Certainly a query of the above form would not benefit from being a union.

For readability, you could use an IN() statement rather than a bunch of ORs
... this would not help performance, but would make your query easier to
type/read.

--
-Josh Berkus
Aglio Database Solutions
San Francisco