MYSQL: UNION – tutorial with examples

We can use the UNION clause in MYSQL when we want to combine the results from multiple select queries and display the combined result in the output.

In this article we will be discussing below topics,

• MYSQL: UNION QUERY SYNTAX
• MYSQL: UNION QUERY EXAMPLE
• MYSQL: UNION RESTRICTIONS
• MYSQL: UNION DISTINCT
• MYSQL: UNION ALL
• MYSQL: UNION ORDER BY

Running multiple queries with the UNION clause will give the output as different records from all the queries, and a single set of common records from these queries. See the below diagram (figure1) for a better understanding.

Let’s say there are two queries: Q1 and Q2

The output of the UNION clause applied on both the queries will be query results from 1st query plus query results from 2nd query plus the single common set of records in both.

From figure1 output of the UNION should be: Q1 results (not in common) + Q2 results(not in common) + single set of CR (common records).

MYSQL: UNION QUERY SYNTAX

SELECT <column1>,<column2>,….. from table1 
UNION
SELECT <column1>,<column2>,….. from table2

Here,<column1>,<column2>,… is a column list we want to select in each query.

MYSQL: UNION EXAMPLE

Before we look into the real-world example from MySQL workbench, let us focus on a small concept to make things clear.

Let’s say we have Table1 and Table2 with some data.

PRO TIP : UNION clause adds the results vertically.

See the UNION RESULTS in figure2, we got five records with duplicates removed, and row with data <2,R> is the common data result set.

We were moving on to a real-world example now. In the below example, we have two tables carrying the data of two different companies A and B and we will be writing a union query to merge the data of a few columns from both to get the results.

 SELECT first_name , emp_category FROM employees_companyA
 UNION
 SELECT first_name , category FROM employees_companyB

Output:-

The record that was common has been added only once <Veronika,
Intern>.

Pay attention to the column names as we can see that the second column name ‘emp_category‘ in the results is taken from the first query ( refer table ’employees_companyA’ ). Let us see what happens on reversing the order of queries and running it again.

 SELECT first_name , category FROM employees_companyB
 UNION
 SELECT first_name , emp_category FROM employees_companyA

Output:-

Again the column name ‘category’ has been taken from first select query.

MYSQL: UNION RESTRICTIONS

There are a few points that should be considered when working with the UNION clause in MySQL.

1. All the queries used with the UNION clause must have the same number of columns.
2. The data type of the columns used in each query should be the same.
3. We cannot use the UNION clause in subqueries.
4. We cannot use the UNION clause with aggregate functions.

MYSQL: UNION DISTINCT

As discussed above, the UNION clause will return the single set of results for the common records, and hence DISTINCT is an optional keyword to be used with UNION in MySQL.
Putting a DISTINCT keyword will make no difference in the results of the query.

For example, again running the queries with UNION clause, will give us the same output as without DISTINCT keyword.

 SELECT first_name , emp_category FROM employees_companyA
 UNION DISTINCT
 SELECT first_name , category FROM employees_companyB

Output:-

MYSQL: UNION ALL

ALL is another keyword which can be used with the UNION clause. The difference it makes to the results is that when ALL is applied to the UNION clause in the query, it will additionally return the duplicate results of multiple queries. Let us see with an example:

Again, we are taking the same tables here but with a different set of data in Table employees_companyB. Table employees_companyB has some more records which are same as in Table employees_companyA (see the records from emp_no 8 to 11).

Observe the query and results in the output.

 SELECT first_name , emp_category FROM employees_companyA
 UNION ALL
 SELECT first_name , category FROM employees_companyB

Output:-

MYSQL: UNION ORDER BY

ORDER BY Clause with UNION operator is used to view the results in a particular order. In the below example we will be viewing the results ordered by first_name.

 SELECT first_name , emp_category FROM employees_companyA
 UNION ALL
 SELECT first_name , category FROM employees_companyB
 ORDER BY first_name

Output:-

We hope this article will help you to build a good understanding on MySQL UNION Clause.