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 BerkusAglio Database SolutionsSan Francisco