Say you’ve got two tables with the same structural layout but contain logically different information. A common example would be storing “deleted” records in a separate table to reduce table sizes, simplify queries, and improve performance. A record only exists in one of the two tables, either it’s deleted or it’s not.
But sometimes you just need to find the information. “I don’t care where record #123 is, I just need to see it.” This situation presents a unique problem. A standard SELECT statement can combine tables to find a unified set of data, but can’t look for the same thing in two different place simultaneously. So you can do the obvious thing, which is the run the same query twice, once on each table. But that’s a way bigger performance penalty than necessary. Get to know the UNION statement…
(SELECT * FROM tbl_stuff WHERE id = '123')
UNION
(SELECT * FROM tbl_stuff_deleted WHERE id = '123')
One query, a single set of results, and performance optimization all wrapped into one. Sweet.