• +91-9142290450

  • Hajipur, Bihar, 844101

☰
❮
❯

MySQL UNION

← Previous Next →

Sometimes, you need to combine results from two or more queries into a single result set. For instance, you may want a list of all customers from different regions stored in separate tables.

The MySQL UNION operator allows you to merge the results of multiple SELECT statements into a single output. This is different from joins, which combine columns from multiple tables; UNION combines rows from separate queries.

What Is UNION in MySQL?

A UNION combines the results of two or more SELECT statements into a single result set.

Key points about UNION:

  • Each SELECT must have the same number of columns.

  • Corresponding columns must have compatible data types.

  • By default, UNION removes duplicate rows. Use UNION ALL to include duplicates.

UNION Syntax

The basic syntax of UNION is:

SELECT column1, column2, ...
FROM table1
WHERE condition
UNION
SELECT column1, column2, ...
FROM table2
WHERE condition;

For UNION ALL:

SELECT column1, column2
FROM table1
UNION ALL
SELECT column1, column2
FROM table2;

Example Tables

Let’s take two tables — north_customers and south_customers.

north_customers

customer_id customer_name city
1 Ananya Delhi
2 Priya Jaipur

south_customers

customer_id customer_name city
3 Isha Bangalore
4 Riya Chennai

Basic UNION Example

To combine all customers into a single list:

SELECT customer_id, customer_name, city
FROM north_customers
UNION
SELECT customer_id, customer_name, city
FROM south_customers;

Result:

customer_id customer_name city
1 Ananya Delhi
2 Priya Jaipur
3 Isha Bangalore
4 Riya Chennai

Explanation:

  • The results of both SELECT statements are merged.

  • Duplicate rows, if any, are removed automatically.

Using UNION ALL

If you want to keep duplicate rows, use UNION ALL:

SELECT customer_name
FROM north_customers
UNION ALL
SELECT customer_name
FROM south_customers;

Result:

customer_name
Ananya
Priya
Isha
Riya

Note:
If “Ananya” existed in both tables, UNION would remove the duplicate, while UNION ALL would keep it.

UNION With Different Conditions

You can filter each SELECT individually. For example, customers from Delhi and Chennai:

SELECT customer_name, city
FROM north_customers
WHERE city = 'Delhi'
UNION
SELECT customer_name, city
FROM south_customers
WHERE city = 'Chennai';

Result:

customer_name city
Ananya Delhi
Riya Chennai

Ordering Results

You can order the combined result using ORDER BY at the end of the UNION:

SELECT customer_name, city
FROM north_customers
UNION
SELECT customer_name, city
FROM south_customers
ORDER BY customer_name ASC;

Result:

customer_name city
Ananya Delhi
Isha Bangalore
Priya Jaipur
Riya Chennai

Important:
The ORDER BY applies to the final result set, not to individual SELECT statements.

UNION With Different Columns

Each SELECT must have the same number of columns. You can use literal values or NULL if one table has fewer columns. For example:

SELECT customer_name, city, 'North' AS region
FROM north_customers
UNION
SELECT customer_name, city, 'South' AS region
FROM south_customers;

Result:

customer_name city region
Ananya Delhi North
Priya Jaipur North
Isha Bangalore South
Riya Chennai South

Explanation:

  • Added a literal value region to indicate the origin of each row.

UNION With Aggregate Functions

You can use UNION with aggregate functions to combine summarized data. For example, total orders from two tables:

SELECT 'North' AS region, SUM(amount) AS total_sales
FROM north_orders
UNION
SELECT 'South' AS region, SUM(amount)
FROM south_orders;

Result:

region total_sales
North 1200
South 950

Explanation:

  • Each SELECT calculates totals for its region, then UNION combines the results.

Performance Considerations

  • UNION removes duplicates by default, which requires additional processing. Use UNION ALL if duplicates are acceptable.

  • Ensure the columns in each SELECT have compatible data types.

  • Avoid unnecessary columns in SELECT to reduce memory usage.

  • Use indexes in WHERE conditions to optimize each SELECT query.

Common Mistakes to Avoid

  1. Different number of columns:
    Each SELECT in a UNION must return the same number of columns.

  2. Incompatible data types:
    Ensure corresponding columns can be compared or merged.

  3. ORDER BY in individual SELECTs:
    Only the final result can have ORDER BY; including it in intermediate SELECTs causes errors.

When to Use UNION

  • To combine rows from multiple tables with similar structures.

  • When consolidating reports or datasets from different sources.

  • When you want a single output from multiple queries without creating temporary tables.

Summary of the Tutorial

The MySQL UNION operator is a simple yet powerful tool to merge rows from different queries into a single result set. By understanding UNION and UNION ALL, you can manage duplicates, order results, and combine data from multiple sources effectively.

Unlike joins, which combine tables horizontally, UNION combines them vertically, allowing for flexible reporting and analysis across multiple tables.

Practice Questions

  1. Write a query to combine all customers from north_customers and south_customers tables into a single list using UNION.

  2. Using UNION ALL, display all orders from online_orders and offline_orders tables, including duplicate order IDs.

  3. Write a query to merge product lists from electronics and furniture tables, showing only unique products.

  4. Using UNION, display all student names from class_A and class_B who scored above 80.

  5. Create a query to list all employees from full_time and part_time tables with their IDs using UNION.

  6. Using UNION, combine city names from north_region and south_region tables, ensuring no duplicates.

  7. Write a query to merge active_customers and inactive_customers tables and include a literal column indicating the customer status.

  8. Using UNION ALL, display all book titles from library_branch1 and library_branch2, including duplicates.

  9. Write a query to combine order amounts from domestic_orders and international_orders tables and calculate the total sum for each source.

  10. Using UNION, display all unique department names from sales_department and marketing_department tables.

← Previous Next →

Try a Short Quiz.

coding learning websites codepractice

No quizzes available.

MySQL

online coding class codepractice

Introduction to MySQL

MySQL SQL Basics

MySQL Functions & Operators

MySQL Joins & Unions

MySQL Grouping & Filtering

Database & Table Management

MySQL Constraints

Date & Views

References

codepractice logo
Login
Go Back Top