A common case is where we may have a archive table containing old data and a current data that only contains the 'active' data. Every so often, the data is moved from active to archive table to keep the indices and row count in a manageable sizes and keep everything zippy.
But suppose I wanted to report on full history of a certain kind of data (e.g. a product or a client) since the first transaction?
I would then UNION the archive table with the active table and I could then collect all data I need from both table.
Note: To clarify for everybody's benefit- it is not always the best idea to have two identical tables, one for archive and one for active, and for most cases, performance can be had with a simple flag indicating it's active or not. This really applies when we are in realms of multi-million rows consuming gigabytes.
Another possible example is when you want to report on all events that happened in the operations, including signing up a new customer, buying, ordering, and selling products. Normally, customers would be in their own table, their order in another table, and your inventory control would be in their set of table. Assuming that all tables has timestamp to indicate when they were added into the database, you could have a UNION query to get all dates from all tables and thus build a full chronological order of events for all different aspects of your operations.
Did that help?