Which Union ALL approach should be used?

Question

We have 15 audit trail tables that need to be combined to look for inventory adjustments with certain criteria only.

Is it best to do a UNION ALL with all tables, and then filter for criteria, OR throw the criteria into the mix on each table before doing a UNION ALL?

Each table is only a few thousand records and the final filtered list is typically less than one thousand, which is being output for reporting.

A foxpro app generates the tables and we have little control of the database design on this end.

Answer 1

It is generally good practice to reduce the row count as early as possible in the query, which means filter each query rather than filter the united resultset. There are chances your UNION ALL set will result in a temporary table being created if you filter after uniting, and this isn't the best if your tables are big (I mean... big).

I think we can accept an exception to the "general good practice" above... In the cases where having the filters at a single point greatly improves readability / maintainability of the query. (And the benefits exceed the potential loss).