Content-Type: text/plain;
  charset="iso-8859-1"
From: Josh Berkus <josh@agliodbs.com>
Reply-To: josh@agliodbs.com
Organization: Aglio Database Solutions
To: Scott Cain <cain@cshl.org>, pgsql-sql@postgresql.org
Subject: Re: OR vs UNION
Date: Thu, 17 Jul 2003 12:00:18 -0700
User-Agent: KMail/1.4.3
References: <1058465506.3345.15.camel@localhost.localdomain>
In-Reply-To: <1058465506.3345.15.camel@localhost.localdomain>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200307171200.18626.josh@agliodbs.com>

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.=20=20

I'd be interested to know where you heard that; as far as I know, it could=
=20
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 =3D 219 OR
>         f.type_id =3D 368 OR
>         f.type_id =3D 514 OR
>         f.type_id =3D 475 OR
>         f.type_id =3D 426 OR
>         f.type_id =3D 456 OR
>         f.type_id =3D 461 OR
>         f.type_id =3D 553 OR
>         f.type_id =3D 89) and
>       fl.srcfeature_id =3D 1 and
>       f.feature_id  =3D fl.feature_id and
>       fl.fmin <=3D 2491413 and fl.fmax >=3D 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=
=20
... this would not help performance, but would make your query easier to=20
type/read.=20

--=20
-Josh Berkus
 Aglio Database Solutions
 San Francisco