Union Operator in SQL - Javagyansite

In this article, we will learn about “Union Operator in SQL” with relevant examples. Let’s get started.

Table of Contents

What is Union Operator in SQL ?

The UNION operator in SQL is used to combine the result sets of two or more SELECT statements into a single result set. It removes duplicate rows by default unless you use the UNION ALL operator.

Simple SQL Union Operator Example: Combining Data from Two Tables

Here’s a simple example of using the UNION operator in SQL with sample data and the corresponding output:

Sample Data:

Let’s say we have two tables, customers and suppliers, with the following data:

Table: customers

customer_id | customer_name
1          | John Doe
2          | Alice Smith
3          | Bob Johnson

Table: suppliers

supplier_id | supplier_name
101         | Eve Brown
102         | Bob Johnson
103         | Grace Lee

We want to combine the names of customers and suppliers into a single list. We can use the UNION operator to do this:

SELECT customer_name
FROM customers
UNION
SELECT supplier_name
FROM suppliers;

The SQL query will produce the following output:

customer_name
John Doe
Alice Smith
Bob Johnson
Eve Brown
Grace Lee

Different Examples of Union Operator in SQL

Let’s look into various use cases of SQL Union Operator with Examples.

Using UNION with ORDER BY

Here’s an example of using the UNION operator with the ORDER BY clause in SQL.

Suppose we have two tables, employees and contractors, with the following data:

Table: employees

employee_id | employee_name
1           | John Doe
2           | Alice Smith
3           | Bob Johnson

Table: contractors

contractor_id | contractor_name
101           | Eve Brown
102           | Grace Lee
103           | Bob Johnson

We want to combine the names of employees and contractors into a single list and order them alphabetically. We can use the UNION operator with the ORDER BY clause for this:

SELECT employee_name
FROM employees
UNION
SELECT contractor_name
FROM contractors
ORDER BY employee_name; -- Order the combined result by employee name

The SQL query will produce the following ordered output:

employee_name
--------------
Alice Smith
Bob Johnson
Eve Brown
Grace Lee
John Doe

In this example, the UNION operator combines the results of the two SELECT statements into a single list of names, and the ORDER BY clause sorts the combined result alphabetically by employee name. It allows you to merge data from two separate tables while controlling the order of the combined result.

Using UNION ALL to Include Duplicate Rows

By default, the UNION operator removes duplicate rows. If you want to include duplicate rows in the result set, you can use the UNION ALL operator.

Here’s an example of using UNION ALL to include duplicate rows in the result, along with sample data and output:

Sample Data:

Suppose you have two tables, sales_january and sales_february, with sales data for two months. Some sales transactions might be duplicated between the two tables.

Table sales_january:

transaction_id	product_name	sale_amount
1	Widget A	100
2	Widget B	150
3	Widget C	75
4	Widget A	120

Table sales_february:

transaction_id	product_name	sale_amount
5	Widget B	130
6	Widget C	80
7	Widget D	200
4	Widget A	120

SQL Query Using UNION ALL:

You can use UNION ALL to combine the sales data from both tables, including duplicate rows:

-- Combine sales data for January and February, including duplicates
SELECT transaction_id, product_name, sale_amount
FROM sales_january
UNION ALL
SELECT transaction_id, product_name, sale_amount
FROM sales_february
ORDER BY transaction_id;

Output:

The result will include all rows from both tables, including duplicates:

transaction_id	product_name	sale_amount
1	Widget A	100
2	Widget B	150
3	Widget C	75
4	Widget A	120
4	Widget A	120
5	Widget B	130
6	Widget C	80
7	Widget D	200

As you can see, the UNION ALL operator combines all rows from both tables, including the duplicate row with transaction_id 4, which appears in both sales_january and sales_february. This is the key difference between UNION and UNION ALL—UNION removes duplicates, while UNION ALL includes all rows, even if they are duplicates.

Using UNION with Aggregate Functions

Here’s an example of using UNION with aggregate functions to combine and aggregate data from two tables, along with sample data and output:

Suppose you have two tables, sales_january and sales_february, with sales data for two months. You want to calculate the total sales for each product across both months.

Table sales_january:

product_name	sale_amount
Widget A	100
Widget B	150
Widget C	75

Table sales_february:

product_name	sale_amount
Widget A	120
Widget B	130
Widget D	200

You can use UNION to combine the sales data from both tables and then apply aggregate functions to calculate the total sales for each product:

-- Calculate total sales for each product across both months
SELECT product_name, SUM(sale_amount) AS total_sales
FROM sales_january
GROUP BY product_name

UNION

SELECT product_name, SUM(sale_amount) AS total_sales
FROM sales_february
GROUP BY product_name
ORDER BY total_sales DESC;

The result will show the total sales for each product across both months:

product_name	total_sales
Widget D	200
Widget B	280
Widget A	220
Widget C	75

In this output, the UNION operator combines and aggregates the sales data for each product from both sales_january and sales_february. It calculates the total sales for each product and orders the results by total sales in descending order.

Conclusion : SQL Union Operator

In this article, we delved into the SQL UNION operator, a powerful tool for combining and manipulating data from multiple tables or queries. We began with a simple example, showcasing how UNION can merge data from two tables into a single result set.

Moving on, we explored various use cases of the UNION operator with informative examples. We demonstrated how to use UNION with the ORDER BY clause to control the sorting of combined data. Additionally, we highlighted the difference between UNION and UNION ALL, emphasizing how UNION ALL includes duplicate rows in the result.

Furthermore, we showcased how UNION can be employed in conjunction with aggregate functions to perform calculations and aggregations across multiple tables. This provides valuable insights into handling and analyzing data from various sources.

In summary, the SQL UNION operator offers tremendous flexibility and utility when it comes to consolidating, transforming, and analyzing data. By mastering its usage, SQL practitioners can unlock the full potential of their database queries and efficiently manipulate data to suit their needs.

Related Articles :