Part 1: Mastering the UNION Command | SQL Challenges & Capstone
SQL Set Operations & Advanced Filtering Practice · Part 1 · Lecture Notes
Mastering the UNION Command
How to combine results from multiple SELECT queries into a single deduplicated output — merging business datasets and building unified reporting lists.
Part 1 of 616% Complete
UNION
Removes Duplicates
Same Column Count
Multi-Query Reporting
8 Practice Questions
Learning Objectives
Use UNION to combine the results of two or more SELECT queries into a single output
Understand that UNION automatically removes duplicate rows from the combined result
Apply the three UNION rules: same column count, compatible data types, consistent column order
Use aliases in the first SELECT to name the combined output column
Click any section header to open or close it
UNION combines the results of two or more SELECT queries into one unified output and automatically removes duplicate rows. It is the standard SQL tool for merging results from different tables or different filtered subsets of the same table into a single list.
In industry, analysts use UNION to combine business reports, merge operational datasets, create unified outputs for dashboards, analyze multiple business activities in one result, and consolidate data from separate queries without writing complex JOIN logic.
Three Rules for UNION
Every SELECT in a UNION must have the same number of columns. The columns must have compatible data types. The columns are matched by position, not by name — so the order must be consistent. The column names in the final output come from the first SELECT.
Basic UNION Syntax
SELECT column_name
FROM table1
UNION
SELECT column_name
FROM table2;
The most common UNION pattern merges a column from one table with the corresponding column from another. The result is a single column list containing values from both sources, with duplicate values removed. Use aliases in the first SELECT to give the combined column a meaningful name.
Alias the First SELECT Only
The output column name comes from the first SELECT. Adding AS combined_results to the first SELECT names the whole output column. The second SELECT does not need an alias — it will be ignored in favour of the first.
Names from Users and Products Combined
SELECT first_name AS combined_results
FROM users
UNION
SELECT product_name
FROM products;
UNION scans the entire combined result and removes any row that appears more than once. If the same value appears in both SELECT results, it appears only once in the final output. This deduplication is what distinguishes UNION from UNION ALL — and it is why UNION is slightly slower on large datasets.
When Deduplication Matters
If user_id 3 appears in both the users table and the subscriptions table, UNION returns 3 only once. UNION ALL would return it twice. Choose UNION when the goal is a unique list; choose UNION ALL when every record including repeats must appear.
Unique user_id Values Across Two Tables
SELECT user_id
FROM users
UNION
SELECT user_id
FROM subscriptions;
UNION is particularly valuable for creating unified reference lists and combined operational summaries. Combining order references and transaction references into one numbered list. Merging product categories and campaign channels. Building a single ID list from multiple business tables. These are all standard UNION patterns in business reporting.
UNION for Financial Value Consolidation
ROUND(amount, 2) UNION ROUND(monthly_fee, 2) merges transaction amounts and subscription fees into one financial values column. Duplicates are removed, giving a clean unique list of all financial values across both tables.
Order and Transaction References Combined
SELECT order_id AS reference_id
FROM orders
UNION
SELECT transaction_id
FROM transactions;
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 campaign_name values from campaigns
AI Prompt
Write a MySQL query to combine first_name values from users and campaign_name values from campaigns using UNION.
SQL Answer
SELECT first_name AS combined_results
FROM users
UNION
SELECT campaign_name
FROM campaigns;
Practice 2Combine order_id values from orders and transaction_id values from transactions
AI Prompt
Write a MySQL query to combine order_id values from orders and transaction_id values from transactions using UNION.
SQL Answer
SELECT order_id AS reference_id
FROM orders
UNION
SELECT transaction_id
FROM transactions;
Practice 3Combine first_name values from users and product_name values from products
AI Prompt
Write a MySQL query to combine first_name values from users and product_name values from products using UNION.
SQL Answer
SELECT first_name AS business_names
FROM users
UNION
SELECT product_name
FROM products;
Practice 4Combine product_name values from products and campaign_name values from campaigns
AI Prompt
Write a MySQL query to combine product_name values from products and campaign_name values from campaigns using UNION.
SQL Answer
SELECT product_name AS combined_names
FROM products
UNION
SELECT campaign_name
FROM campaigns;
Practice 5Combine user_id values from users and subscriptions
AI Prompt
Write a MySQL query to combine user_id values from users and subscriptions using UNION.
SQL Answer
SELECT user_id
FROM users
UNION
SELECT user_id
FROM subscriptions;
Practice 6Combine ticket_id values from support_tickets and order_id values from orders
AI Prompt
Write a MySQL query to combine ticket_id values from support_tickets and order_id values from orders using UNION.
SQL Answer
SELECT ticket_id AS business_reference
FROM support_tickets
UNION
SELECT order_id
FROM orders;
Practice 7Combine category values from products and channel values from campaigns
AI Prompt
Write a MySQL query to combine category values from products and channel values from campaigns using UNION.
SQL Answer
SELECT category AS business_category
FROM products
UNION
SELECT channel
FROM campaigns;
Practice 8Combine transaction amounts and monthly subscription fees rounded to 2 decimal places
AI Prompt
Write a MySQL query to combine transaction amounts and monthly subscription fees rounded to 2 decimal places using UNION.
SQL Answer
SELECTROUND(amount, 2) AS financial_values
FROM transactions
UNION
SELECTROUND(monthly_fee, 2)
FROM subscriptions;
Knowledge Check
Five questions — select the best answer for each.
1. What does UNION do with duplicate rows in the combined result?
AIt keeps all duplicates to preserve the complete record count
BIt automatically removes duplicate rows from the combined result
CIt raises an error when duplicates are detected
DIt moves duplicates to a separate output column
Correct. UNION automatically deduplicates the combined result — if the same value appears in both SELECT queries, it appears only once in the final output. Use UNION ALL to preserve duplicates.
Not quite. UNION removes duplicate rows automatically. If a value appears in both queries, it appears once in the output.
2. Which of the following must be true for two SELECT statements to be combined with UNION?
ABoth must select from the same table
BBoth must have identical column names
CBoth must have the same number of columns with compatible data types
DBoth must use an ORDER BY clause
Correct. UNION requires each SELECT to have the same number of columns in the same order, with compatible data types. Column names do not need to match — the output uses the names from the first SELECT.
Not quite. UNION requires the same number of columns in the same order with compatible data types. The tables and column names can differ.
3. Where do the output column names come from in a UNION result?
AThey are automatically generated by MySQL
BThey come from the last SELECT statement
CThey come from the first SELECT statement
DBoth SELECT statements must use identical column names
Correct. The output column names in a UNION result always come from the first SELECT. Aliases in the first SELECT name the combined column. Aliases in subsequent SELECTs are ignored.
Not quite. UNION uses the column names (or aliases) from the first SELECT statement for the output.
4. What is the key difference between UNION and UNION ALL?
AUNION works across tables; UNION ALL only works within the same table
BUNION removes duplicates; UNION ALL keeps all records including duplicates
CUNION is slower than UNION ALL only on small tables
DUNION ALL requires an ORDER BY clause; UNION does not
Correct. UNION deduplicates the combined result; UNION ALL keeps every row including repeated ones. UNION ALL is faster because it skips the deduplication step.
Not quite. The key difference is deduplication — UNION removes duplicates, UNION ALL keeps all records.
5. An analyst runs UNION to combine user_id from users and user_id from subscriptions. If user_id 3 exists in both tables, how many times does 3 appear in the result?
ATwice — once from each table
BZero times — overlapping values are excluded
COnce — UNION removes the duplicate
DIt depends on the ORDER BY clause
Correct. UNION removes duplicate rows. Since user_id 3 appears in both SELECT results, UNION deduplicates it and keeps only one occurrence in the final output.
Not quite. UNION removes duplicates — if user_id 3 appears in both tables, it appears once in the UNION result.
Part 1 Summary
The key ideas from this lecture — keep these in mind as you move forward.
UNION helps analysts combine multiple query results into a single unified business output while automatically removing duplicate records. The three rules: same column count, compatible types, consistent order. Column names come from the first SELECT.
UNIONCombine QueriesRemove DuplicatesSame Column CountCompatible TypesAlias First SELECTUnified Output
