Manny said the following on 31/07/06 09:07:

One big con for many people is the need to remember that it's 'UNION
ALL' if you don't want duplicate rows to be removed from the result set.

Will

I assume you are asking specifically about Ingres SQL and not the wider
relational theory.   Have you read the relevant sections of the manual
already?  (If not, you can find it (as a fairly weighty download) at
http://downloads.ingres.com/download/sql.pdf.)

When would you use a union query?  That depends on you and your database
design, mostly.  I think it's possible that some designs will require more
unions than others.

Generally, if you have a situation where you could use a union, or not, you
should first think about how clearly using a union reveals your intention
(so your code will be more easily maintainable).  You shouldn't use a union
just because you can.  Nor should you avoid using one if you have to write a
hugely complicated query to do it.  There will be cases where using a union
will be slower than an alternate formulation.  If it is too slow, then you
have to make the decision to possibly sacrifice clarity for speed.

I note that there are a number of places where you ought to be able to use a
union and Ingres just doesn't allow it, for no good reason.  For example,
you are not allowed to use a union in a select expression (e.g. a
subselect).  That limitation regularly annoys me.

One final comment: by default, union is the *duplicate free* union of the
queries.  That means there will always implicitly be a duplicate removal
operation (possibly a sort, possibly something else).  The duplicate removal
process is potentially expensive for very large result sets.  If you know,
based on what you know about the database, that the subqueries in the union
are all disjoint, you can use UNION ALL to skip the duplicate removal
process.  Personally I wouldn't ever do that though (unless I had absolutely
no choice).

Roy