At 12:04 AM 10/02/2005 +0100, you wrote:

>Maybe "union all" will work? Since "union" may return duplicates it
>doesn't really make sense to allow this.

UNION ALL may return duplicates. UNION alone discards duplicates.

btw, my comment re multi-row returns wasn't a propos for this particular
question. Sorry about that.

However, the optimiser doesn't, as you suppose, form a set of results for
the IN(<subquery expression>) syntax. It resolves IN (<subquery
expression>) to an EXISTS(<subquery expression>). Absent the ability to
recognise a UNION subquery and resolve it to a series of EXISTS(<>) OR
EXISTS(<>)..., such resolution could not get past the first SELECT in the
union. Note, too, that if the ORing were supported, it would make no
difference whether the union was UNION ALL or just UNION.

EXISTS() exits with True immediately upon finding a match. The presence (or
not) or duplicates doesn't matter in an EXISTS() subquery, unless the
catchment is enormous and the EXISTS() test is either going to fail or is
going to find the first positive for the set at the remote end of a poorly
indexed set.

./hb