Re: Still More, Re: Questions about RPG service program as an SQL UDF

On 01 Mar 2013 18:05, James H. H. Lampert wrote:

<<SNIP>> The query is actually 3 queries, UNION ALL'd together
(not quite sure why UNION'd together isn't good enough). <<SNIP>>

Actually to be UNION'd is more [work] than to be UNION ALL'd ;-)

The specification of UNION keyword is synonymous with having used UNION DISTINCT. The specification of UNION ALL asks the query engine not to expend any effort to additionally ensure that all rows are DISTINCT.

This is in contrast to the select-clause for which SELECT is synonymous with SELECT ALL. Coding SELECT DISTINCT is how to ask the query engine to perform the additional work of DISTINCT processing for the rows.

For consistency, IMO, the UNION and SELECT both should have defaulted to perform ALL processing when an optional additional keyword DISTINCT was not coded. Silly standards.