A Comprehensive Guide to SQL Unions

KolaKachi
This entry is part 18 of 19 in the series SQL Course For Absolute Beginners

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:

CustomerIDCustomerNameContactEmail
1Customer A[email protected]
2Customer B[email protected]
3Customer C[email protected]

Suppliers Table:

SupplierIDSupplierNameContactEmail
101Supplier X[email protected]
102Supplier Y[email protected]
103Supplier Z[email protected]

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:

EntityNameContactEmail
Customer A[email protected]
Customer B[email protected]
Customer C[email protected]
Supplier X[email protected]
Supplier Y[email protected]
Supplier Z[email protected]

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:

EntityNameContactEmail
Customer A[email protected]
Customer B[email protected]
Customer C[email protected]
Supplier X[email protected]
Supplier Y[email protected]
Supplier Z[email protected]

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:

EntityNameContactEmail
Customer A[email protected]
Customer B[email protected]
Customer C[email protected]
Supplier X[email protected]
Supplier Y[email protected]
Supplier Z[email protected]
Customer A[email protected]
Supplier X[email protected]
Customer B[email protected]

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.

Series Navigation<< A Comprehensive Guide to GROUP BY and HAVING Clause in SQLComprehensive Guide to SQL Joins >>

Leave a Reply

Your email address will not be published. Required fields are marked *