A Comprehensive Guide to SQL Unions

In SQL, the UNION operator is a powerful tool that allows you to combine rows from multiple result sets into a single result set. This operation is particularly useful when you need to retrieve data from different tables or queries that have the same structure. In this comprehensive guide, we'll explore SQL UNION with practical examples and table results to help you understand and utilize it effectively.

Understanding the UNION Operator

The UNION operator is used to combine the result sets of two or more SELECT statements. It removes duplicate rows by default, ensuring that the final result set contains only distinct rows. To use UNION, the following conditions must be met:

• The number of columns in each SELECT statement must be the same.

• The data types of corresponding columns in each SELECT statement must be compatible.

Let's dive into various examples to illustrate how to use the UNION operator.

Example Tables

Throughout this guide, we will use two sample tables: Customers and Suppliers. Both tables have the same structure, containing information about entities and their respective contact details. Here are the tables:

Customers Table:

Suppliers Table:

Example 1: Basic UNION

Let's start with a basic example. Suppose you want to retrieve a list of all entities (customers and suppliers) along with their contact details. You can achieve this using a UNION between the Customers and Suppliers tables:

SQL Query:

SELECT CustomerName AS EntityName, ContactEmail
FROM Customers
UNION
SELECT SupplierName AS EntityName, ContactEmail
FROM Suppliers;

Table Results:

In this query, we retrieve the customer and supplier names along with their contact emails, combining the results using UNION.

Example 2: UNION with ORDER BY

You can also use UNION with an ORDER BY clause to sort the combined result set. For instance, let's retrieve a list of all entities, ordered by their names:

SQL Query:

SELECT CustomerName AS EntityName, ContactEmail
FROM Customers
UNION
SELECT SupplierName AS EntityName, ContactEmail
FROM Suppliers
ORDER BY EntityName;

Table Results:

In this query, we use ORDER BY to sort the combined result set alphabetically by the entity names.

Example 3: UNION ALL

By default, UNION removes duplicate rows from the result set. If you want to include duplicate rows, you can use UNION ALL. Let's see an example where we retrieve all entities, including duplicates:

SQL Query:

SELECT CustomerName AS EntityName, ContactEmail
FROM Customers
UNION ALL
SELECT SupplierName AS EntityName, ContactEmail
FROM Suppliers;

Table Results:

In this query, we use UNION ALL, which includes duplicate rows in the result set.

Conclusion

The UNION operator in SQL is a valuable tool for combining results from multiple SELECT statements into a single result set. Whether you need to merge data from different tables, eliminate duplicate rows, or sort the combined data, UNION provides flexibility and efficiency in your database queries. By mastering UNION and its variations, you can retrieve and manipulate data from various sources effectively in your SQL applications.