as a follow up.

UNION goes out and collects all the records and then does a DISTINCT
operation on the result set. If your result set is a couple of thousands
records, this can add lots of time to the query. UNION ALL does
not do a DISTINCT operation. It returns all the records from each
part of the UNION statement whether they are duplicates are not.

Hope this helps.

Thomas Steinmaurer wrote:

>Rolf,
>
>
>
>>thanks for your help. I'll use
>>
>> select LocationId, LocationName from Locations
>> union
>> select -5, Cast('(new location)' as VarChar(64)) from Locations
>> order by 2;
>>
>>which works fine. The second part of the union returns only one row even
>>when referencing table Locations.
>>
>>
>
>Yes, because you are using UNION and not UNION ALL. It might
>be faster when using the system table RDB$DATABASE, because
>it contains only one record.
>
>
>select LocationId, LocationName from Locations
>union
>select -5, Cast('(new location)' as VarChar(64)) from RDB$DATABASE
>order by 2;
>
>
>Regards,
>Thomas Steinmaurer
>http://www.iblogmanager.com
>
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>
>
>

--
Thomas Miller
Delphi Client/Server Certified Developer
BSS Accounting & Distribution Software
BSS Enterprise Accounting FrameWork

http://www.bss-software.com
http://sourceforge.net/projects/dbexpressplus