Combining two tables is not always the same as collecting their distinct values. Before choosing UNION or UNION ALL, decide whether two identical-looking rows represent one fact or two events.
In PostgreSQL’s set-operation documentation, UNION removes duplicate result rows, while UNION ALL retains them. The queries must return compatible column counts and types. Neither choice substitutes for an explicit ORDER BY when order matters.
Imagine two stalls each recording a sale of one apple. Both export only the product column, so each file contains a row saying “apple.” UNION returns one apple row; UNION ALL returns two. If the question is “which products were sold?”, the first result may be appropriate. If it is “how many sales were recorded?”, discarding the second row loses an event.
Check the selected columns before blaming the operator
The duplicate comparison concerns the rows you select. Adding a source label changes those rows. A result containing “stall A, apple” differs from “stall B, apple,” even though both mention the same product. That distinction is useful when you need to trace where a record came from.
For a small local test, SELECT 'apple' AS item UNION SELECT 'apple' returns one row. Replace UNION with UNION ALL to retain both. These are illustrative queries containing no production data.
Keep a count before and after combining the real inputs. If two sets of 100 events should produce 200 events, an unexpectedly smaller result deserves investigation. Conversely, retaining all rows is not proof that the inputs themselves contain no accidental duplicates.
Write down the intended unit of a row: a product name, a transaction, or a transaction line. Once that sentence is clear, the operator becomes a consequence of your data model rather than a guess about which result looks tidier.