SQL Union: Explained with Examples

Introduction

Wise decisions require the ability to extract insightful information from a range of sources. Accessing data from diverse tables is often a necessity if you are analyzing customer behavior, tracking sales trends, or performing any data-related tasks. This is where the SQL UNION command comes into play.

The SQL UNION operator or command serves as a bridge between different datasets. It allows you to easily aggregate the results of many SELECT operations performed on columns from different tables. 

I will show you how to use the SQL UNION command in this article by providing clear explanations and valuable examples.

Overview

Through the use of SQL UNION, you can simplify your data analysis and extract insightful information from several datasets. You can do it by combining the results of two or more SELECT command searches.

This guide will not only help you have a solid understanding of SQL UNION, but it will also help you with its application within your data analysis procedures. To start with the basics, we will cover the syntax and key concepts of SQL Union.

What is SQL Union?

SQL Union is a strong SQL command that combines the results of numerous SELECT queries into a single distinct result set. This operator is quite useful when you need to integrate data from many sources or tables.

The SQL union syntax is described below.

SELECT column1, column2, ...

FROM table1

UNION

SELECT column1, column2, ...

FROM table2;

In this syntax:

• SELECT column1, column2, ... specifies the columns you want to retrieve from the tables that are involved in the union operation.
• FROM table1 and FROM table2 denote the tables from which you're selecting data.
• UNION is the keyword that combines the results of the two SELECT queries.

Now let’s look at some fundamental SQL union examples

1. Simple Union of Two Queries

Let's say you have two tables, employees and customers, each containing information about different entities.

Employees Table

Customers Table;

You wish to get a list of names from the two tables. Here's how you can accomplish this using SQL Union:

SELECT name FROM employees

UNION

SELECT name FROM customers;

This query will combine the names from both tables and remove any duplicates, which gives you a unified list of names across employees and customers.

Output:

2. Union with Alias Names for Columns

In some cases, you may want to provide custom names for columns in the resulting union. This can be achieved using column aliases by the same example as before:

SELECT name AS entity_name FROM employees

UNION

SELECT name AS entity_name FROM customers;

By specifying AS entity_name, you ensure that the name columns from both of the tables are combined under a single alias, making the result set more readable and intuitive.

Output:

3. Using Union to Combine Results from Multiple Tables

SQL UNION is not limited to just two tables—you can combine results from multiple tables in a single union operation. For instance, using the same example as before, suppose you have additional tables such as suppliers and partners, each containing relevant information.

Suppliers Table:

Partners Table

You can include them in the union like so:

SELECT name FROM employees

UNION

SELECT name FROM customers

UNION

SELECT name FROM suppliers

UNION

SELECT name FROM partners;

This query aggregates names from all four tables, which further creates a comprehensive list of entities across different categories.

Output:

Combining SQL Union With Other Subqueries

Combining SQL Union with other subqueries opens up a world of possibilities for data manipulation and analysis. Let's explore three key scenarios where SQL UNION can be enhanced with subqueries:

1. Union with WHERE Clause

When using SQL UNION with a WHERE clause, you can filter the results of each SELECT query before combining them. This allows you to focus on specific subsets of data and refine your final result set.

Consider a scenario where you have two tables: ‘sales_2021’ and ‘sales_2022’, each containing sales data for different years.

Table 1: Sales Data for 2021 (sales_2021)

Table 2: Sales Data for 2022 (sales_2022)

You want to combine the sales figures for products that cost more than $100, and you want to do it from both tables. Here's how you can achieve this using SQL UNION with a WHERE clause:

SELECT product_name, sales_amount

FROM sales_2021

WHERE price > 100

UNION

SELECT product_name, sales_amount

FROM sales_2022

WHERE price > 100;

Output table:

2. Union with ORDER BY Clause

By combining SQL UNION with an ORDER BY clause, you can sort the final result set according to your preferred criteria. This allows you to arrange the combined data in a meaningful and structured way, which enhances readability and analysis.

For example, Let's say you want to merge employee records from two tables, ‘employees_1’ and ‘employees_2’,

Table 1: Employee Records (employees_1)

Table 2: Employee Records (employees_2)

In case, you want to display the results sorted by employee ID in ascending order, here's how you can accomplish this using SQL Union with an ORDER BY clause:

SELECT employee_id, employee_name

FROM employees_1

UNION

SELECT employee_id, employee_name

FROM employees_2

ORDER BY employee_id ASC;

Output table

 3. Combining SQL Union with SQL JOIN:

SQL UNION can also be combined with SQL JOIN operations to further enhance data integration and analysis. By joining tables before using Union, you can incorporate additional filtering and data enrichment, leading to more comprehensive insights.

For example;

Suppose you have two tables, ‘customers’ and ‘orders’, and you want to combine customer information with order details for last month’s buyers.

Table 1: Customer Information (customers)

Table 2: Order Details (orders)

You can do this by first joining the tables based on customer IDs and then applying Union:

SELECT customer_name, order_date, order_amount

FROM customers

JOIN orders ON customers.customer_id = orders.customer_id

WHERE order_date >= '2023-02-01' AND order_date < '2023-03-01'

UNION

SELECT customer_name, order_date, order_amount

FROM customers_archive

JOIN orders_archive ON customers_archive.customer_id = orders_archive.customer_id

WHERE order_date >= '2023-02-01' AND order_date < '2023-03-01';

Output Table:

Comparison of SQL UNION With Other Similar SQL Queries

When working with SQL, you often encounter scenarios where you need to combine data from multiple sources to derive meaningful insights. Just like SQL UNION, SQL offers several powerful commands for this purpose, including SQL UNION and UNION ALL, INTERSECT, EXCEPT, and JOIN operations. While these operations share some similarities, they also have distinct features and use cases.

Conclusion

In summary, the SQL UNION operator serves as an important tool in database management. It facilitates seamless integration of data from various sources. Throughout this article, you have acquired a thorough comprehension of the SQL UNION operator, its syntax, and its practical applications through illustrative examples.

We have explored how SQL UNION enables the efficient merging of data from multiple tables. Furthermore, we looked into advanced techniques such as combining UNION with other subqueries, including WHERE clauses, ORDER BY clauses, and SQL JOIN operations. This will help to further refine and augment your data analysis capabilities.

There is no doubt that by mastering SQL UNION, you are well-prepared to address a myriad of data manipulation challenges.

FREQUENTLY ASKED QUESTIONs

1. What does the UNION do in SQL?

The UNION operator in SQL combines the outcomes of two or more SELECT queries into just one outcome set. It effectively merges the rows from each query, while automatically removing duplicates.

2. What is the distinction between a JOIN and a UNION in SQL?

In SQL, a JOIN is used to combine columns from two or more tables based on a related column. It essentially creates a Cartesian product of the tables involved. On the other hand, UNION is used to combine the results of two or more SELECT queries into a single result set, removing duplicates automatically.

3. Is UNION costly in SQL?

The cost of using UNION in SQL depends on various factors such as the size of the datasets, the complexity of the queries, and the database engine that is being used. Generally, UNION operations can be less efficient compared to other operations like UNION ALL since they involve removing duplicates.

4. What is UNION and distinct in SQL?

In SQL, UNION merges the outcomes of two or more SELECT queries into a single result set while deleting duplicates. DISTINCT, on the other hand, is used within a single SELECT query to eliminate duplicate rows from the result set.

5. Is UNION faster in SQL?

The speed of a UNION operation in SQL depends on factors, such as the size of the datasets and the database engine in use. Generally, UNION ALL is faster than UNION since it does not involve removing duplicates, but this may vary based on the specific circumstances.

6. Does UNION include duplicates?

No, by default, the UNION operator in SQL removes duplicate rows from the result set. If you want to retain duplicates, you can use the UNION ALL operator instead.

7. How can I eliminate duplicate rows while keeping one copy in SQL?

To delete duplicate rows while keeping one copy in SQL, you can use the ROW_NUMBER() function along with a common table expression (CTE) or a subquery. Here's a basic example:

WITH CTE AS (

SELECT column1, column2, ..., ROW_NUMBER() OVER (PARTITION BY column1, column2, ... ORDER BY column1) AS RowNumber

FROM your_table

)

DELETE FROM CTE WHERE RowNumber > 1;

This query assigns row numbers to each row, partitioned by the columns you specify and deletes rows where the row number is greater than 1, effectively keeping only one copy of each duplicate row.