>>>>Could it be that the definition of faster includes the time to transmit the resulting data set? In that case, if UNION ALL results in (say) 1,000,000 records while UNION results in only 5000 the transmission time for the 995000 extra records is more than the time to filter them?
>>>
>>>Yes, I think you nail the possible problem.
>>
>>When you use SELECT in VFP all tables included in that SELECT is transmitted to local computer. And that means ALL, no matter hat you select and what fields you select, whole tables are downloaded here. So the downloading time is not an option.
>>Using other backend like SQL Server that is a matter of "How smart SQL Optimizer is"
>>Just my opinion.
>
>I don't think that is accurate. When using a backend, the query is run on the backend and only the resultset is sent. I tried the "WHERE 1=0" on a table which has 16000 records (MySQL not SQL Server). You could see it returning the 16,000 records. Add the selection and it just blinked. Tried it again in reversed sequence (just in case there was caching going on) and got same result. Only the resultset is sent down the line.

I talk about VFP not for other backends.