Databases are not just about data volumes and storage, equally important is how the data is organized and stored. Today, the relational model, which utilizes interconnected tables, is the most widely adopted and highly effective approach to data storage in databases.
The next challenge is to retrieve the data from that storage and ensure it is exactly what we need here and now. The SQL language we use to interact with databases offers plenty of effective operators to achieve this. One of them is UNION.
This article delves into the UNION clause and its applications in MySQL, the leading relational database management system, providing insights on its definition, benefits, and effective usage.
Contents
- MySQL UNION operator: definition and purpose
- MySQL UNION vs. JOIN
- MySQL UNION vs. UNION ALL
- Practical examples and use cases
- MySQL UNION performance and optimization
- Visualize and execute MySQL UNIONs with GUI tools
- Alternatives to UNION in MySQL
- Conclusion
MySQL UNION operator: definition and purpose
Relational database management systems suggest storing data in tables that relate to each other. Usually, we keep our data in different tables and need to retrieve it and combine it into a single output. The MySQL UNION operator allows us to perform this operation easier than by using numerous SELECT statements with the WHERE conditions and then combining their results manually.
What does the UNION operator do? It combines the results of several SELECT commands, removes duplicate results, and creates a single result set.
That final result set contains all rows returned by the SELECT statements involved in that UNION query but without duplicate values. The column names in the combined result set will be the same as in the first SELECT statement. In the above example, the “A-1” record is present in both tables, so the UNION operator removes the duplicated record.
The syntax of MySQL UNION
The basic syntax of the UNION clause used in the SELECT query is as follows:
SELECT column1, column2, column3,... column_N
FROM table1
UNION
SELECT column1, column2, column3,.. column_N
FROM table2;Parameters
column1, column2… column_N – columns that we want to retrieve. There can be any number of columns specified in the query.
table1, table2, etc. – tables we query to fetch the records from them. You need at least one table in the FROM clause. Note that the UNION operator can retrieve data from the same table if needed.
Rules and restrictions of MySQL UNION
When using UNION with MySQL databases, you need to follow several rules:
- The number of columns in each SELECT query within the combined UNION query must be the same. We get an error if the SELECT statements used in the UNION query have a different number of columns.
- The respective columns in every SELECT statement must be of the same data type.
- The resulting dataset will feature the same number of columns as the first SELECT statement, and the column names of the first SELECT statement will be applied as the column names of the dataset.
- None of the SELECT statements can contain columns with NULL values.
- Using UNION in subqueries is not allowed.
The UNION clause serves best when you combine the data from several similar tables, such as active and archived data, lists of students from different groups, lists of purchases, etc.
MySQL UNION vs. JOIN
The JOIN clause is, perhaps, the most popular clause used to combine data from several tables into one result set. However, if we compare JOIN with UNION in MySQL, we see significant differences in the methods of data combining.
| JOIN | UNION |
| Is used to extract data from several tables. | Is used to combine the results of several SELECT statements. |
| Presents joined records as new columns. | Presents combined results as new rows. |
| Combines tables horizontally. | Combines tables vertically. |
| Allows duplicate values. | Removes duplicate values by default. |
| Requires at least one common column for the joined tables. | Requires a similar number of columns and matching data types in all SELECT queries. |
We can visualize the work of the JOIN clause vs. the UNION clause graphically as below:
MySQL UNION vs. UNION ALL
Using the UNION operator deletes identical values in the result set. In some cases, however, it is not acceptable. We need all rows, including duplicate records. To get all the data, we’ll need to use the modified operator – UNION ALL.
UNION ALL works similarly to UNION – it combines the results of several SELECT queries into a single result set. However, UNION ALL contains duplicate records too.
The basic syntax of UNION ALL with several SELECT statements is as follows:
SELECT column1, column2, column3,... column_N
FROM table1
UNION ALL
SELECT column1, column2, column3,.. column_N
FROM table2;The parameters, rules of usage, and restrictions are the same for UNION ALL as for the simple UNION operator. We have defined them earlier in this article.
Another aspect worth noticing is the performance of the UNION and UNION ALL operators. UNION ALL is faster because it does not spend resources and time checking results to remove duplicates.
Practical examples and use cases
Let us review use cases for MySQL UNION and UNION ALL clauses in practice. To illustrate these practical examples, we’ll use the well-known MySQL sakila test database and dbForge Studio for MySQL – a multi-featured MySQL GUI tool that allows us to write effective SQL queries easily and perform all other database-related tasks on these database management systems.
Example 1: Basic UNION
In the first example, we want to retrieve all unique addresses from the results of two SELECT commands.
SELECT
address,
CONCAT_WS(' ',first_name,last_name) Customer_Name
FROM address
INNER JOIN customer
ON customer.address_id = address.address_id
UNION
SELECT
address,
CONCAT_WS(' ',first_name,last_name) Customer_Name
FROM address_out
INNER JOIN customer
ON customer.address_id = address_out.address_id;
Example 2: UNION ALL
The below example is a query that combines the results of two SELECT commands. The first one retrieve film titles and their release years (in this case, it is 2006). The second SELECT statement fetches the names of categories (the release year is set as 0 because this value is not applied to the category name).
With UNION ALL, we combine the results of these two SELECTs into one set. We also sort it by the release year and title using the ORDER BY clause for better readability.
SELECT title, release_year
FROM film
WHERE release_year = 2006
UNION ALL
SELECT name, 0 AS release_year
FROM category
ORDER BY release_year DESC, title ASC;
Example 3: UNION with a condition
This scenario suggests using UNION with the WHERE clause to filter results. The first SELECT command requests names, last names, and email addresses of buyers who used store 1. Another SELECT query fetches the same data for all buyers who used store 2. With UNION, we can make a single result set containing the data about the clients of both stores.
Note: As we are using UNION, and not UNION ALL, the results set will have only unique values. That’s why, even if some person is the client of both stores, their name will appear once only in the results.
SELECT CONCAT_WS(' ',first_name,last_name) Customer_Name, email
FROM customer
WHERE store_id = 1
UNION
SELECT CONCAT_WS(' ',first_name,last_name) Customer_Name, email
FROM customer
WHERE store_id = 2;
Example 4: UNION with DISTINCT
In this case, we use UNION with DISTINCT to combine the results of two SELECT queries: the first query brings the list of cities in Texas, and the second query brings the list of cities in California. By applying DISTINCT to each SELECT, we are removing duplicate city names from the final result set.
SELECT DISTINCT city
FROM address,city
WHERE address.city_id=city.city_id AND district = 'Texas'
UNION
SELECT DISTINCT city
FROM address,city
WHERE address.city_id=city.city_id AND district = 'California';
Example 5: Simple UNION for keyword search
Here we apply UNION to combine several SELECT results where each query retrieves film titles and descriptions containing the keyword “action” in them. Pay attention that using UNION means removing all duplicate values. Thus, films matching the search criteria will be displayed once in the final result set.
SELECT title, description
FROM film
WHERE title LIKE '%action%'
UNION
SELECT title, description
FROM film
WHERE description LIKE '%action%';
Example 6: UNION for multiple keyword searches
In this example, we have two SELECT queries, and each one should bring the list of films matching the search keyword “action” or “adventure.” Each SELECT fetches titles and descriptions of those films having the words “action” or “adventure” in them.
SELECT title, description
FROM film
WHERE title LIKE '%action%' OR title LIKE '%adventure%'
UNION
SELECT title, description
FROM film
WHERE description LIKE '%action%' OR description LIKE '%adventure%';
MySQL UNION performance and optimization
Performance speed and efficiency are key metrics for every query we compose. We have plenty of practical methods to accelerate queries on MySQL and reduce their cost. Speaking of UNION and UNION ALL queries, we can appeal to the following methods.
Add indexes to columns
Indexing is the universal method of database optimization. Though indexes in MySQL are physical objects stored separately, their effectiveness in speeding queries up compensates for the disk space. Add indexes to columns, especially those used with the WHERE, ORDER BY, and GROUP BY clauses. This will accelerate performance significantly.
Avoid using DISTINCT in the UNION queries
The DISTINCT clause is optional in the UNION query syntax. The purpose of that clause is to remove duplicate values in the result set. However, the UNION operator removes identical records by default, and using DISTINCT does not bring anything to the query.
Avoid LIKE with preceding wildcards
If your query includes the LIKE operator with leading wildcards, it forces the query to ignore present indexes. As a result, it runs longer and consumes more resources performing the full-table scan. When the database is large, it can be a problem. So, don’t include such elements as LIKE ‘%mysearch%’ in your UNION query.
Query the necessary columns only
It is another universal recommendation. Scanning wide tables requires a lot of time and CPU resources. That’s why always specify which columns you need to process and fetch the data from them.
Give preference to UNION ALL over UNION where applicable
The main factor that increases the time and CPU costs is the process of removing duplicate values from the SELECT results. It requires an additional scan of all results to identify and remove duplicates. If your work scenario does not demand unique values in the combined result set, use UNION ALL as the faster and more effective option.
The basic optimization tips for MySQL queries work for the UNION operator precisely. And, of course, there are some specific recommendations. The best option is to evaluate your MySQL UNION queries before executing them. Modern database tools with graphical interfaces offer this functionality.
Visualize and execute MySQL UNIONs efficiently using GUI tools
dbForge Studio for MySQL includes the Query Profiler tool that collects the statistics about query performance and helps database admins identify problems, resolve them, and streamline queries before executing. Among the many features, Query Profiler offers the EXPLAIN plan with the following capabilities:
- Easy detection of bottlenecks in queries
- Easy detection of time delays at different query execution stages
- Tracing and diagnosing causes of slow queries
- Profiling queries and comparing their results in a visual mode
This execution plan lets the users evaluate the UNION/UNION ALL queries (and any other queries they might need) before executing them and spending resources. The quality analysis allows the database specialists to rewrite any MySQL queries to make them faster.
It is recommended to always analyze the query before executing it. Be it a MySQL UNION or another one, apply the EXPLAIN plan first, identify any problematic areas, and fix them before running the script. Document your results to save them for further reference, and monitor the query performance daily. Query Profiler provides all the necessary functionality besides the EXPLAIN plan to let you tune queries up and achieve high performance.
Besides, don’t forget about coding assistants to write clean and highly efficient code much faster. Auto-completion, syntax checking, code debugging, and formatting along with Query Builder that offers the most convenient method of constructing complex queries in a visual mode are always at your service with dbForge Studio for MySQL.
Alternatives to UNION in MySQL
Fetching specific data portions from different tables and combining them according to some criteria is a regular routine for all database users. MySQL offers various means – operators – to reach that goal. We have well-known JOINs and also UNIONs. Besides, it is worth mentioning such operators as INTERSECT and EXCEPT.
The INTERSECT operator
MySQL INTERSECT allows you to query multiple tables and return a single result set containing only those records that appear in all SELECT statements involved.
We can visualize the INTERSECT operator’s work as follows:
The basic syntax of the INTERSECT query is:
SELECT column1, column2, column3,... column_N
FROM table1
INTERSECT
SELECT column1, column2, column3,.. column_N
FROM table2;The EXCEPT operator
MySQL EXCEPT allows us to query several tables using the SELECT command and acquire records that appear in the first SELECT query result only. It lets us find the differences between the data in two tables or define unique records in the table.
We can visualize the work of the EXCEPT operator work as follows:
The basic syntax of the EXCEPT operator is:
SELECT column1, column2, column3,... column_N
FROM table1
EXCEPT
SELECT column1, column2, column3,... column_N
FROM table2;Both INTERSECT and EXCEPT remove duplicate values from the combined result set by default. The column names and the number of columns in the output are the same as in the first SELECT statement.
Conclusion
MySQL UNION is a helpful tool that allows us to fetch and merge data from different queries and present it appropriately for further work. That’s why it is essential to understand how the UNION operator works, its benefits and limitations, and how to apply it correctly, especially for large databases.
GUI tools are handy for all database users who need more efficient methods of dealing with the UNION (and other) operators on MySQL. Such a popular GUI solution as dbForge Studio for MySQL we used to illustrate our article offers plenty of features to simplify the users’ lives.
In particular, the Query Builder tool helps you construct even the most complex queries easily, optimization features suggest improvements to speed queries up and make them more effective, and the overall visual representation illustrates the results of the UNION operations in the most user-friendly way.
