SQL UNION and UNION ALL Practice Questions with Solutions

The SQL UNION and UNION ALL operators are used to combine the results of two or more SELECT queries into a single result set. SQL UNION and UNION ALL Practice questions with solutions help to build concepts.

These operators are useful when data is stored in multiple tables with the same structure, such as:

• Multiple company branches
• Monthly sales tables
• Archived and current records
• Different warehouses
• Student batches

Instead of running separate queries, you can merge the results into one report.

Difference Between UNION and UNION ALL

Rules for Using UNION

Before using UNION, remember these rules:

• Every SELECT statement must have the same number of columns.
• Corresponding columns must have compatible data types.
• Column names in the final result are taken from the first SELECT statement.

Sample Tables

batch_a

batch_b

1. SQL UNION to Combine Students from Two Batches

Problem Statement

A training institute stores students in two separate tables:

• Batch A
• Batch B

Write an SQL query to display the names of all students from both batches.

SQL Solution

SELECT student_name
FROM batch_a

UNION

SELECT student_name
FROM batch_b;

Sample Output

Explanation

The first query retrieves students from Batch A.

The second query retrieves students from Batch B.

The UNION operator combines both results into one list while removing duplicate values if they exist.

Concepts Covered

• UNION
• Combining Multiple Queries
• Removing Duplicates

2. SQL UNION to Combine Employee Lists from Two Company Branches

Problem Statement

A company has two office branches:

• Delhi
• Noida

Write an SQL query to display all employee names.

Sample Tables

delhi_branch

noida_branch

SQL Solution

SELECT employee_name
FROM delhi_branch

UNION

SELECT employee_name
FROM noida_branch;

Sample Output

Explanation

The UNION operator merges employee names from both branches into a single result set.

Duplicate names (if any) would appear only once.

Concepts Covered

• UNION
• Multiple Tables
• Employee Database

3. SQL UNION to Display Customer Cities from Two Databases

Problem Statement

A retail company stores customers in two regional databases.

Display all unique customer cities.

Sample Tables

north_customers

south_customers

SQL Solution

SELECT city
FROM north_customers

UNION

SELECT city
FROM south_customers;

Sample Output

Explanation

Although Delhi exists in both tables, it appears only once because UNION automatically removes duplicate values.

Concepts Covered

• UNION
• DISTINCT Results
• Customer Database

4. SQL UNION to Combine Product Lists from Two Warehouses

Problem Statement

An e-commerce company stores inventory in two different warehouses.

Write an SQL query to display a unique list of all available products from both warehouses.

Sample Tables

warehouse_a

warehouse_b

SQL Solution

SELECT
    product_name

FROM warehouse_a

UNION

SELECT
    product_name

FROM warehouse_b;

Sample Output

Explanation

The UNION operator combines products from both warehouses into one list.

Although Mouse exists in both tables, it appears only once because UNION removes duplicate records.

This query is useful for:

• Inventory Reports
• Warehouse Management
• Stock Availability
• Product Catalog Generation

Concepts Covered

• UNION
• Removing Duplicate Records
• Inventory Database

5. SQL UNION to Combine Teacher Records from Two Schools

Problem Statement

An educational organization manages two schools.

Write an SQL query to display a unique list of teachers from both schools.

Sample Tables

school_a

school_b

SQL Solution

SELECT
    teacher_name

FROM school_a

UNION

SELECT
    teacher_name

FROM school_b;

Sample Output

Explanation

The teacher Suman appears in both schools.

Since the query uses UNION, duplicate names are removed automatically.

This type of report is commonly used by:

• Educational Groups
• School Management Systems
• Teacher Allocation Reports

Concepts Covered

• UNION
• Duplicate Elimination
• School Database

Difference Between UNION and UNION ALL

Consider the following example.

warehouse_a

warehouse_b

Using UNION

SELECT product_name
FROM warehouse_a

UNION

SELECT product_name
FROM warehouse_b;

Result

Duplicate values are removed.

Using UNION ALL

SELECT product_name
FROM warehouse_a

UNION ALL

SELECT product_name
FROM warehouse_b;

Result

Duplicate values are not removed.

6. SQL UNION ALL to Display Orders from Two Different Stores

Problem Statement

A retail company has two physical stores.

Management wants to generate a report showing all customer orders from both stores.

Even if the same customer places orders in both stores, every order should appear.

Write an SQL query using UNION ALL.

Sample Tables

store_a_orders

store_b_orders

SQL Solution

SELECT
    customer_name,
    amount

FROM store_a_orders

UNION ALL

SELECT
    customer_name,
    amount

FROM store_b_orders;

Sample Output

Explanation

Unlike UNION, UNION ALL does not remove duplicate records.

Rahul appears twice because he placed orders at both stores.

This is useful for:

• Sales Reports
• Order Tracking
• Revenue Analysis

Concepts Covered

• UNION ALL
• Duplicate Records
• Sales Database

7. SQL UNION ALL to Combine Monthly Sales Reports

Problem Statement

A company stores sales data separately for January and February.

Generate a report containing all sales transactions.

Sample Tables

january_sales

february_sales

SQL Solution

SELECT
    salesperson,
    amount

FROM january_sales

UNION ALL

SELECT
    salesperson,
    amount

FROM february_sales;

Sample Output

Explanation

The salesperson Aman appears twice because he made sales in both months.

UNION ALL preserves every sales transaction.

Concepts Covered

• UNION ALL
• Monthly Reports
• Sales Analytics

8. SQL UNION ALL to Merge Website Visitor Logs

Problem Statement

A website stores visitor logs in separate tables for desktop and mobile users.

Generate a report containing all website visits.

Sample Tables

desktop_visitors

mobile_visitors

SQL Solution

SELECT
    visitor_name

FROM desktop_visitors

UNION ALL

SELECT
    visitor_name

FROM mobile_visitors;

Sample Output

Explanation

Rahul visited from both desktop and mobile.

Since each visit is important, UNION ALL keeps both records.

Concepts Covered

• UNION ALL
• Visitor Tracking
• Website Analytics

9. SQL UNION ALL to Display Customer Support Tickets

Problem Statement

A company maintains customer support tickets in two branches.

Display all support tickets from both branches.

Sample Tables

delhi_support

noida_support

SQL Solution

SELECT
    customer_name

FROM delhi_support

UNION ALL

SELECT
    customer_name

FROM noida_support;

Sample Output

Explanation

Rahul raised support tickets in both branches.

Each support request must remain separate, making UNION ALL the correct choice.

Concepts Covered

• UNION ALL
• Customer Support
• Ticket Management

10. SQL UNION ALL to Combine Inventory Transactions

Problem Statement

An inventory management system stores warehouse transactions separately.

Display all inventory transactions from both warehouses.

Sample Tables

warehouse_a_transactions

warehouse_b_transactions

SQL Solution

SELECT
    product_name

FROM warehouse_a_transactions

UNION ALL

SELECT
    product_name

FROM warehouse_b_transactions;

Sample Output

Explanation

The product Laptop appears twice because inventory transactions from both warehouses must be preserved.

This type of report is commonly used for:

• Stock Movement Reports
• Warehouse Audits
• Inventory Management

Concepts Covered

• UNION ALL
• Inventory Reports
• Warehouse Database

11. SQL UNION with ORDER BY

Problem Statement

A university stores student records in two different tables:

• Morning Batch
• Evening Batch

Display a combined list of students and sort the result alphabetically.

Sample Tables

morning_batch

evening_batch

SQL Solution

SELECT
    student_name

FROM morning_batch

UNION

SELECT
    student_name

FROM evening_batch

ORDER BY student_name;

Sample Output

Explanation

The ORDER BY clause is written after the final SELECT statement.

It sorts the combined result returned by the UNION.

Concepts Covered

• UNION
• ORDER BY
• Sorting Combined Results

12. SQL UNION with WHERE Clause

Problem Statement

A company has two branches.

Display employees whose salary is greater than ₹50,000 from both branches.

Sample Tables

delhi_employees

noida_employees

SQL Solution

SELECT
    employee_name,
    salary

FROM delhi_employees

WHERE salary > 50000

UNION

SELECT
    employee_name,
    salary

FROM noida_employees

WHERE salary > 50000;

Sample Output

Explanation

The WHERE clause filters records before the UNION operation.

Only employees earning more than ₹50,000 are included in the final result.

Concepts Covered

• UNION
• WHERE
• Filtering Data

13. SQL UNION Using Column Aliases

Problem Statement

A retail company stores customer information in two databases.

Display a single report using meaningful column names.

Sample Tables

online_customers

offline_customers

SQL Solution

SELECT
    customer_name AS customer

FROM online_customers

UNION

SELECT
    customer_name

FROM offline_customers;

Sample Output

Explanation

The alias defined in the first SELECT statement becomes the column name of the final result.

Aliases improve readability in reports and dashboards.

Concepts Covered

• UNION
• Column Alias
• Reporting

14. SQL UNION with Aggregate Functions

Problem Statement

A company wants to calculate the total sales for two separate quarters.

Display the combined total sales.

Sample Tables

q1_sales

q2_sales

SQL Solution

SELECT
    SUM(amount) AS total_sales

FROM q1_sales

UNION

SELECT
    SUM(amount)

FROM q2_sales;

Sample Output

Explanation

Each SELECT statement calculates the total sales for one quarter.

UNION combines both summary results into a single report.

Concepts Covered

• UNION
• Aggregate Functions
• SUM()

15. SQL UNION to Generate a Multi-Source Business Report

Problem Statement

A company wants to create a report showing all active customers from:

• Website registrations
• Mobile application registrations

Display one combined list.

Sample Tables

website_users

mobile_users

SQL Solution

SELECT
    customer_name

FROM website_users

UNION

SELECT
    customer_name

FROM mobile_users;

Sample Output

Explanation

The same customer may register using multiple platforms.

Since UNION removes duplicate values automatically, Rahul appears only once in the final report.

This type of query is commonly used in:

• CRM Systems
• Customer Analytics
• Business Intelligence Dashboards
• Marketing Reports

Concepts Covered

• UNION
• Duplicate Removal
• Business Reporting

Chapter Summary

In this chapter, you learned how to combine the results of multiple SQL queries using the UNION and UNION ALL operators.

These operators are extremely useful when data is stored in multiple tables with the same structure, such as different branches, warehouses, monthly reports, or archived records.

Throughout this chapter, you practiced:

• Combining records from multiple tables using UNION
• Removing duplicate records automatically with UNION
• Preserving duplicate records using UNION ALL
• Using ORDER BY with UNION
• Using WHERE clauses with UNION
• Using column aliases in combined queries
• Combining aggregated results
• Building real-world business reports from multiple data sources

These concepts are commonly used in reporting systems, business intelligence dashboards, and SQL interview questions.

Key Takeaways

• UNION combines the results of two or more SELECT statements.
• UNION automatically removes duplicate rows.
• UNION ALL combines results without removing duplicates.
• UNION ALL is generally faster than UNION because it skips duplicate elimination.
• All SELECT statements must return the same number of columns.
• Corresponding columns should have compatible data types.
• ORDER BY is written only once at the end of the final query.
• WHERE clauses can be applied independently within each SELECT.
• UNION is commonly used for combining data from different branches, departments, or time periods.
• Understanding the difference between UNION and UNION ALL is a frequent SQL interview topic.

Frequently Asked Questions (FAQs)

1. What is SQL UNION?

The UNION operator combines the results of two or more SELECT statements and removes duplicate rows.

Example:

SELECT student_name
FROM batch_a

UNION

SELECT student_name
FROM batch_b;

2. What is SQL UNION ALL?

UNION ALL combines the results of multiple queries without removing duplicate records.

Example:

SELECT customer_name
FROM store_a

UNION ALL

SELECT customer_name
FROM store_b;

3. What is the difference between UNION and UNION ALL?

4. What conditions must be satisfied before using UNION?

Before using UNION:

• Both queries must return the same number of columns.
• The corresponding columns must have compatible data types.
• The column names in the final result come from the first query.

5. Can I use ORDER BY with UNION?

Yes.

The ORDER BY clause is written after the last SELECT statement.

Example:

SELECT student_name
FROM batch_a

UNION

SELECT student_name
FROM batch_b

ORDER BY student_name;

6. Can I use WHERE with UNION?

Yes.

Each SELECT statement can have its own WHERE clause.

Example:

SELECT employee_name
FROM delhi_employees
WHERE salary > 50000

UNION

SELECT employee_name
FROM noida_employees
WHERE salary > 50000;

7. When should I use UNION instead of JOIN?

Use UNION when you want to append rows from multiple queries.

Use JOIN when you want to combine related columns from different tables based on matching keys.

8. Where are UNION and UNION ALL used in real-world applications?

They are commonly used in:

• Multi-branch company reports
• Monthly sales reports
• Warehouse inventory reports
• Banking transaction history
• CRM systems
• Student databases
• Hospital records
• Business Intelligence dashboards
• Financial reports
• Data migration projects

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar