Context

Teams often store similar records in separate tables or queries, such as regional sales, archived transactions, or data from different business units. Interviewers ask about UNION to test whether you understand how to stack compatible result sets correctly.

Core Question

Explain how you would use UNION to combine datasets with similar structure. In your answer, cover:

1. What requirements the two queries must satisfy
2. The difference between UNION and UNION ALL
3. When duplicate removal is desirable or harmful
4. How you would align column names, data types, and filters before combining results

Scope Guidance

The interviewer is usually looking for a practical explanation with a small SQL example, not just a definition. You should be able to describe the syntax, common mistakes, and how this applies to reporting or data consolidation tasks.

Column Compatibility

Each SELECT in a union must return the same number of columns, in the same order, with compatible data types. The final output column names are taken from the first SELECT, so consistent aliasing matters for readability.

SELECT order_id, customer_id, amount
FROM current_orders
UNION
SELECT order_id, customer_id, amount
FROM archived_orders;

UNION vs UNION ALL

UNION removes duplicate rows after combining result sets, while UNION ALL keeps all rows. UNION ALL is usually faster because it avoids the extra deduplication step.

SELECT email FROM newsletter_signups
UNION ALL
SELECT email FROM event_signups;

Deduplication Semantics

Duplicate removal happens across the full selected row, not just one column. Two rows are considered duplicates only if every selected column value matches.

SELECT customer_id, signup_source
FROM source_a
UNION
SELECT customer_id, signup_source
FROM source_b;

Ordering Combined Results

You generally apply ORDER BY once at the end of the full unioned query, not inside each individual SELECT. This sorts the final combined result set rather than each source independently.

SELECT product_name, price FROM store_a_products
UNION ALL
SELECT product_name, price FROM store_b_products
ORDER BY price DESC;

Schema Alignment

When source tables are similar but not identical, you may need aliases, casts, or placeholder values like NULL to make the result sets compatible. This is common when combining current and legacy data.

SELECT customer_id, order_date, amount, 'current' AS source
FROM current_orders
UNION ALL
SELECT customer_id, created_at AS order_date, total_amount AS amount, 'archive' AS source
FROM archived_orders;