Practical technology learning
SQL UNION and UNION ALL: Write the Duplicate Policy Before Combining Results
A repeated row can represent an accidental overlap or useful evidence. Before combining query results, decide what one output row represents. The following small, fictional activity exports make that choice visible without using customer or business records.
Define two tiny exports
Create one single-column practice result containing R1 and R2, and another containing R2 and R3. The column represents an activity code. There are four input rows and three distinct codes across both results. Keep those counts beside your query before testing anything.
Compare the two output contracts
Combine the results with UNION and expect R1, R2 and R3, without relying on their presentation order. Change only the operator to UNION ALL and expect four rows, including two occurrences of R2. This comparison is about duplicate retention, not joining related columns side by side.
Inspect the entire selected row
Now select a source label as well as the activity code: A beside the first export and B beside the second. The two R2 rows have different source values. They are different selected rows, so UNION can keep both. Adding a diagnostic column can change deduplication behavior even when the identifier column looks unchanged.
Choose a meaning before choosing an operator
If the question asks which activity codes appeared anywhere, a distinct-code result can be appropriate. If it asks how many observations were imported, retaining occurrences may be necessary. Neither query proves whether two records describe the same real-world event. That decision needs a reliable business key and a defined reconciliation rule.
Review the query boundary
- Use the same number of columns with compatible types on both sides.
- Write the meaning of one output row in plain language.
- Compare expected counts before and after adding source fields.
- Use an explicit final ORDER BY when presentation order matters.
Make the result reviewable
Keep both query versions and the fictional inputs together. Add one sentence explaining why repeated observations are preserved or collapsed. Set operations belong to the Mango Engineers SQL learning path. A clearly stated output contract makes the query easier for another learner to review.