Part 2: Mastering the UNION ALL Command | SQL Challenges & Capstone
SQL Set Operations & Advanced Filtering Practice · Part 2 · Lecture Notes
Mastering the UNION ALL Command
How to combine complete query outputs while preserving all records including duplicates — stacking operational data for full business coverage.
Part 2 of 633% Complete
UNION ALL
Preserves Duplicates
Faster Than UNION
Operational Stacking
8 Practice Questions
Learning Objectives
Use UNION ALL to combine query results while preserving all records including duplicates
Understand that UNION ALL is faster than UNION because it skips the deduplication step
Apply UNION ALL for operational data stacking, audit trails, and complete frequency analysis
Know when to choose UNION ALL (preserve all records) vs UNION (unique deduplicated list)
Click any section header to open or close it
UNION ALL combines the results of two or more SELECT queries into one output without removing any rows. Unlike UNION, it does not scan for or remove duplicates — every record from every SELECT appears in the final result, including repeated values. UNION ALL is faster than UNION for this reason.
In industry, analysts use UNION ALL to stack complete operational datasets, preserve repeated business activity, combine full reporting outputs without deduplication, analyze all transactional records in chronological sequence, and build cumulative datasets for audit trails and full record analysis.
UNION ALL vs UNION
UNION removes duplicate rows — slower, produces fewer rows. UNION ALL keeps every row — faster, produces more rows. Use UNION ALL when every record matters, including repeats. Use UNION when you need a unique deduplicated list.
Basic UNION ALL Syntax
SELECT column_name
FROM table1
UNION ALL
SELECT column_name
FROM table2;
UNION ALL produces more rows than UNION when the combined queries share common values. If transaction_type contains “Purchase” seven times and ticket_status contains “Resolved” five times, UNION ALL returns all twelve rows. UNION would deduplicate “Completed” if it appeared in both — UNION ALL would not.
When Preserving Duplicates Is Correct
Stacking audit logs, combining transaction records from different systems, merging all campaign events and all sessions — in all these cases, duplicates are valid data that must not be removed. UNION ALL is the correct choice whenever the full count matters.
All Transaction Types and Ticket Statuses Stacked
SELECT transaction_type AS business_status
FROM transactions
UNION ALL
SELECT ticket_status
FROM support_tickets;
Operational data stacking — combining all records from two or more sources into one unified dataset — is one of the most frequent UNION ALL use cases. All order IDs plus all transaction IDs gives a complete reference list of all financial activity. All subscription user_ids plus all user user_ids gives a complete customer activity record.
Stack Then Aggregate
A common analytics pattern is to UNION ALL multiple tables, wrap the result in a subquery, and then COUNT, SUM, or GROUP BY on the combined output. UNION ALL produces the full stacked dataset; the outer aggregation then summarizes it.
All Orders and Transactions Stacked
SELECT order_id AS reference_id
FROM orders
UNION ALL
SELECT transaction_id
FROM transactions;
When analysts need complete visibility across multiple datasets — every category from products alongside every channel from campaigns, every financial value from two different tables — UNION ALL ensures nothing is dropped. Every row from every SELECT is included in the output.
Financial Value Stacking
ROUND(amount, 2) UNION ALL ROUND(monthly_fee, 2) produces a column containing every transaction amount and every subscription fee — including repeated values. If three transactions share the same amount, all three appear. This is useful for distribution analysis and frequency counting.
All Financial Values Stacked
SELECTROUND(amount, 2) AS financial_values
FROM transactions
UNION ALL
SELECTROUND(monthly_fee, 2)
FROM subscriptions;
Work through each question below. Run your query first, then use Check Answer. A wrong answer reveals the correct SQL with a Copy button; a correct result earns a congratulations message.
Practice 1Combine first_name values from users and product_name values from products using UNION ALL
AI Prompt
Write a MySQL query to combine first_name values from users and product_name values from products using UNION ALL.
SQL Answer
SELECT first_name AS combined_results
FROM users
UNION ALL
SELECT product_name
FROM products;
Practice 2Combine order_id values from orders and transaction_id values from transactions using UNION ALL
AI Prompt
Write a MySQL query to combine order_id values from orders and transaction_id values from transactions using UNION ALL.
SQL Answer
SELECT order_id AS reference_id
FROM orders
UNION ALL
SELECT transaction_id
FROM transactions;
Practice 3Combine transaction_type values from transactions and ticket_status values from support_tickets using UNION ALL
AI Prompt
Write a MySQL query to combine transaction_type values from transactions and ticket_status values from support_tickets using UNION ALL.
SQL Answer
SELECT transaction_type AS business_status
FROM transactions
UNION ALL
SELECT ticket_status
FROM support_tickets;
Practice 4Combine category values from products and channel values from campaigns using UNION ALL
AI Prompt
Write a MySQL query to combine category values from products and channel values from campaigns using UNION ALL.
SQL Answer
SELECT category AS business_category
FROM products
UNION ALL
SELECT channel
FROM campaigns;
Practice 5Combine user_id values from users and subscriptions using UNION ALL
AI Prompt
Write a MySQL query to combine user_id values from users and subscriptions using UNION ALL.
SQL Answer
SELECT user_id
FROM users
UNION ALL
SELECT user_id
FROM subscriptions;
Practice 6Combine ticket_id values from support_tickets and order_id values from orders using UNION ALL
AI Prompt
Write a MySQL query to combine ticket_id values from support_tickets and order_id values from orders using UNION ALL.
SQL Answer
SELECT ticket_id AS business_reference
FROM support_tickets
UNION ALL
SELECT order_id
FROM orders;
Practice 7Combine campaign_name values from campaigns and product_name values from products using UNION ALL
AI Prompt
Write a MySQL query to combine campaign_name values from campaigns and product_name values from products using UNION ALL.
SQL Answer
SELECT campaign_name AS combined_names
FROM campaigns
UNION ALL
SELECT product_name
FROM products;
Practice 8Combine transaction amounts and monthly subscription fees rounded to 2 decimal places using UNION ALL
AI Prompt
Write a MySQL query to combine transaction amounts and monthly subscription fees rounded to 2 decimal places using UNION ALL.
SQL Answer
SELECTROUND(amount, 2) AS financial_values
FROM transactions
UNION ALL
SELECTROUND(monthly_fee, 2)
FROM subscriptions;
Knowledge Check
Five questions — select the best answer for each.
1. What does UNION ALL do that UNION does not?
AUNION ALL requires matching column names across all SELECT statements
BUNION ALL keeps all records including duplicate rows
CUNION ALL only works when selecting from the same table
DUNION ALL sorts the output alphabetically
Correct. UNION ALL keeps every row from every SELECT including repeated values. UNION removes duplicates. UNION ALL is faster because it skips the deduplication step.
Not quite. The key difference is UNION ALL preserves duplicates — it keeps every row from every SELECT without removing repeated values.
2. Which is faster: UNION or UNION ALL?
AUNION — because it produces fewer rows
BUNION ALL — because it skips the deduplication step
CThey perform identically on all datasets
DUNION is faster on small tables; UNION ALL is faster on large tables
Correct. UNION ALL skips the deduplication scan, making it faster than UNION. For large datasets where duplicates are acceptable or expected, UNION ALL is always preferred for performance.
Not quite. UNION ALL is faster because it doesn’t scan for duplicates. UNION requires an extra deduplication step, making it slower.
3. An analyst stacks user_id from users (8 rows) and user_id from subscriptions (8 rows) using UNION ALL. How many rows appear in the result?
A8 rows — UNION ALL still deduplicates shared values
BUp to 8 rows — only unique values
C16 rows — UNION ALL keeps all records from both tables
DDepends on how many user_ids match between the tables
Correct. UNION ALL keeps every row from every SELECT. 8 rows from users plus 8 rows from subscriptions = 16 rows in the result. No deduplication occurs.
Not quite. UNION ALL keeps all rows. 8 + 8 = 16 rows in the combined result regardless of any matching values.
4. When should an analyst choose UNION ALL over UNION?
AWhen they want a deduplicated list of unique values
BWhen they need to keep all records including repeated values, or when performance on large datasets matters
CWhen the two SELECT statements select from the same table
DWhen the combined column must be sorted
Correct. UNION ALL is preferred when every record must be counted including repeats — audit trails, stacked operational data, frequency analysis. It is also preferred for large datasets where the deduplication cost of UNION is significant.
Not quite. Choose UNION ALL when all records including duplicates must appear, or when performance is a priority.
5. What do UNION and UNION ALL have in common?
ABoth remove duplicate rows from the combined result
BBoth require the same number of columns with compatible data types in each SELECT
CBoth require an ORDER BY clause at the end
DBoth only work with numeric columns
Correct. Both UNION and UNION ALL require each SELECT to have the same number of columns in the same order with compatible data types. The only difference is how they handle duplicates.
Not quite. Both UNION and UNION ALL share the same structural rules: same column count, compatible types, consistent order.
Part 2 Summary
The key ideas from this lecture — keep these in mind as you move forward.
UNION ALL helps analysts combine complete query outputs while preserving all records, including duplicates. Faster than UNION because no deduplication is performed. Choose UNION ALL when every record must be counted, including repeated values.
UNION ALLPreserves DuplicatesFaster Than UNIONOperational StackingAll RecordsFrequency AnalysisComplete Coverage
