MySQL UNION Operator

The UNION operator is used to combine the result-set of two or more SELECT statements. The MySQL UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements. MySQL Union is an operator that allows us to combine two or more results from multiple SELECT queries into a single result set. It comes with a default feature that removes the duplicate rows from the result set.

• Every SELECT statement within UNION must have the same number of columns
• The columns must also have similar data types
• The columns in every SELECT statement must also be in the same order

UNION Syntax

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

UNION ALL Syntax

The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL:SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

Parameters or Arguments

expression1, expression2, … expression .The columns or calculations that you wish to retrieve. tables. The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause. WHERE conditions Optional. The conditions that must be met for the records to be selected. DISTINCT Optional. Removes duplicates from the result set, but the inclusion of the DISTINCT modifier has no impact on the result set of the UNION operator because, by default, the UNION operator already removes duplicates.

Note: The column names in the result-set are usually equal to the column names in the first SELECT statement.

How do I create a UNION in MySQL?

MySQL UNION operator allows you to combine two or more result sets of queries into a single result set. The following illustrates the syntax of the UNION operator: SELECT column_list UNION [DISTINCT | ALL] SELECT column_list UNION [DISTINCT | ALL] SELECT column_list …

Is UNION an operator in SQL?

The Union operator combines the results of two or more queries into a distinct single result set that includes all the rows that belong to all queries in the Union. In this operation, it combines two more queries and removes the duplicates.

Demo Database

In this tutorial we will use the well-known Northwind sample database. Below is a selection from the “Customers” table:

And a selection from the “Suppliers” table:

SQL UNION Example

The following SQL statement returns the cities (only distinct values) from both the “Customers” and the “Suppliers” table:

Example

SELECT City FROM Customers<br>UNION<br>SELECT City FROM Suppliers<br>ORDER BY City;

Note: If some customers or suppliers have the same city, each city will only be listed once, because UNION selects only distinct values. Use UNION ALL to also select duplicate values!

SQL UNION ALL Example

The following SQL statement returns the cities (duplicate values also) from both the “Customers” and the “Suppliers” table:

Example

SELECT City FROM Customers<br>UNION ALL<br>SELECT City FROM Suppliers<br>ORDER BY City;

SQL UNION With WHERE

The following SQL statement returns the German cities (only distinct values) from both the “Customers” and the “Suppliers” table:

Example

SELECT City, Country FROM Customers<br>WHERE Country='Germany'<br>UNION<br>SELECT City, Country FROM Suppliers<br>WHERE Country='Germany'<br>ORDER BY City;

SQL UNION ALL With WHERE

The following SQL statement returns the German cities (duplicate values also) from both the “Customers” and the “Suppliers” table:

Example

SELECT City, Country FROM Customers<br>WHERE Country='Germany'<br>UNION ALL<br>SELECT City, Country FROM Suppliers<br>WHERE Country='Germany'<br>ORDER BY City;

Another UNION Example

The following SQL statement lists all customers and suppliers:

Example

SELECT 'Customer' AS Type, Contact Name, City, Country<br>FROM Customers<br>UNION<br>SELECT 'Supplier', ContactName, City, Country<br>FROM Suppliers;

Notice the “AS Type” above – it is an alias. SQL Aliases are used to give a table or a column a temporary name. An alias only exists for the duration of the query. So, here we have created a temporary column named “Type”, that list whether the contact person is a “Customer” or a “Supplier”.

More Examples to Implement MySQL UNION ALL

Let us evaluate and illustrate some of the examples showing working and results of the MySQL UNION ALL operator in the database upon the related tables:

Examples

Let us consider two tables for implementing the query statements using both operators. First of all, we will create table Person with fields Person_ID, Person_Name, Person_Address and again creating second table Customer with fields CustomerID, CustomerName, Credit_Limit, City. We will perform the following SQL queries to create database tables for demonstrating the examples:

Code:

CREATE TABLE Person(Person_ID INT PRIMARY KEY AUTO_INCREMENT, Person_Name VARCHAR(255) NOT NULL, Person_Address VARCHAR(255) NOT NULL);
CREATE TABLE Customer(CustomerID INT PRIMARY KEY AUTO_INCREMENT, CustomerName VARCHAR(255) NOT NULL, Credit_Limit DECIMAL(10,2) NOT NULL, City VARCHAR(255) NOT NULL);

Suppose, we have entered some records as sample for the tables Person and Customer with the help of the MySQL query statements below respectively:

Code:

INSERT INTO Person(`Person_ID`, `Person_Name`, `Person_Address`) VALUES ('101','Akash','Delhi');
INSERT INTO Customer(`CustomerID`, `CustomerName`, `Credit_Limit`, `City`) VALUES ('101','Nikhil','2800.00','Delhi');

We can view the records as follows:

Code:

SELECT * FROM Person;

Output:

SELECT * FROM Customer;

Output:

1. UNION ALL operator vs UNION operator

We are writing the following code executing the UNION operator to fetch the city address values from both the tables which are distinct rows in the result combined set:

Code:

SELECT Person_Address FROM Person
UNION
SELECT City FROM Customer;

Output:

Also, let us execute the query above with UNION ALL operator which provides the resultant table which contains a combination of column values from both the tables:

Code:

SELECT Person_Address FROM Person
UNION ALL
SELECT City FROM Customer;

Output:

It is clear from the results that with UNION operator we have received the discrete values and with UNION ALL operator we have valued having identical rows. Here, we have used similar values from Person_Address and City columns from Person and Customer tables simultaneously.

2. UNION ALL operator example to fetch single field

For this example, let us again create a similar table named Employee as Person having a field in common with identical data type also to perform the UNION ALL query. CREATE TABLE Employee(Person_ID INT PRIMARY KEY AUTO_INCREMENT, Employee_Name VARCHAR(255) NOT NULL, Salary INT NOT NULL, Join Date DATE NOT NULL);

Inserting some values into it:

Code:

INSERT INTO Employee(`Person_ID`, `Employee_Name`, `Salary`, `JoinDate`) VALUES ('101','Akash,'1045','2020-05-01');

Now, we have the UNION ALL operator query as follows:

Code:

SELECT Person_ID FROM Person
UNION ALL
SELECT Person_ID FROM Employee;

Output:

3. UNION ALL operator example with ORDER BY clause& WHERE option

Here, we will apply for ORDER BY Clause and WHERE option together with the UNION ALL operator in the query to order the resultant rows of the combined table with one of the field values:

Code:

SELECT Person_ID, Person_Name FROM person WHERE Person_Address = 'Delhi'
UNION ALL
SELECT CustomerID, CustomerName FROM Customer WHERE Credit_Limit> 1000 ORDER BY 2;

Output:

Advantages

• Since the operator does not remove any duplicates from the result set and pulls every row values from the related tables that fit your query essentials to combine to one table so, the MySQL UNION ALL operator works much faster as compared to the MySQL UNION operator.
• While developing reports in the database server, you do not need duplicates in the result but if the UNION is applied then, the server needs to perform additional action to avoid the replicas. So UNION ALL can be a better option if possible for performance-based.
• This blocking operator may be useful in some cases to find out the duplicity among the related tables in the MySQL database.
• It helps to merge multiple tables’ records in a time-consuming manner to result in table with specific fields but for UNION to provide distinctive rows it requires comparisons which takes time.

Conclusion

• If we use MySQL UNION ALL operator to our query using different SELECT statements which are compatible in structure to associate the result sets together then, we will receive the duplicate table values with it.
• Unlike the UNION operator, in UNION ALL operator query the result values are not filtered to omit the distinct union sets as output.