Using UNION operator in MySQL 8: The Complete Guide

Introduction

MySQL is a widely-used open-source relational database management system that facilitates the efficient storage and retrieval of data. One of the powerful features in MySQL is the UNION operator, which is used to combine the result set of two or more SELECT statements. In this guide, we will delve deep into the usage of the UNION operator in MySQL 8 through a series of practical examples from basic to advanced levels, highlighting the benefits of using UNION in various scenarios.

Understanding the UNION Operator

The UNION operator in MySQL allows you to combine the results of two or more SELECT statements into a single result set. Each SELECT statement within the UNION must have the same number of columns, and those columns must have compatible types. Additionally, the columns must also be in the same order for a successful operation.

Syntax:

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

The UNION operator by default removes duplicate rows from the result set. To keep duplicates, UNION ALL can be used instead of UNION.

Syntax for UNION ALL:

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Now, let’s start with some code examples.

Basic Example of UNION

To start with a simple scenario, imagine we have two tables named customers and suppliers each with a field for names. We want to create a list of all names from both tables, without duplicates.

Code:

SELECT name FROM customers
UNION
SELECT name FROM suppliers;

Output:

Name
----
Alpha Inc
Bravo LLC
Charlie Group
...

Including Duplicates with UNION ALL

If we want to include duplicates in the results, we can do this by using UNION ALL.

Code:

SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;

Output:

Name
----
Alpha Inc
Alpha Inc
Bravo LLC
...

Note that ‘Alpha Inc’ might appear twice if it exists in both tables.

Sorting Results in a UNION Query

You can also add an ORDER BY clause to sort the entire result set of a UNION query.

Code:

SELECT name FROM customers
UNION
SELECT name FROM suppliers
ORDER BY name;

Output:

Name
----
Alpha Inc
Bravo LLC
Charlie Group
...

Advanced UNION Queries

Let’s make the queries a bit more complex. Assume we have another table called orders, and we want to select all customers and suppliers who have made orders over $10,000, again avoiding duplicates.

Code:

SELECT customer_name AS name FROM orders WHERE total > 10000
UNION
SELECT supplier_name AS name FROM orders WHERE total > 10000;

Output:

Name
----
High Roller Inc
Moneybags Co
...

Join Operations with UNION

You can use the UNION operator in conjunction with JOIN operations to combine results from multiple table associations.

Code:

SELECT customers.name FROM customers
INNER JOIN orders ON customers.id = orders.customer_id
UNION
SELECT suppliers.name FROM suppliers
INNER JOIN orders ON suppliers.id = orders.supplier_id;

Output:

Name
----
Alpha Inc
Bravo LLC
Charlie Group
...

UNION with Aggregate Functions

The UNION operator can be used to combine the results from aggregate functions, like COUNT, SUM, AVG, etc., from multiple tables or queries.

Code:

SELECT 'Total customers', COUNT(*) FROM customers
UNION
SELECT 'Total suppliers', COUNT(*) FROM suppliers;

Output:

Description       Count
----------------- ------
Total customers   150
Total suppliers   75

Conclusion

The UNION operator in MySQL 8 allows for the effective merging of similar datasets into a cohesive result set, useful for streamlining data analysis tasks. With careful column matching and type compatibility, you can create powerful and customizable queries to serve virtually any data retrieval requirement.