MySQL UNION ALL

Updated May 25, 2023

Introduction to MySQL UNION ALL

• MySQL UNION ALL operator is a union query command which syndicates multiple SELECT statements’ results into a single result row.
• Like, the MySQL UNION operator, it is also a useful command in MySQL database to combine more than two of the output set provided by using SELECT queries. But there is slightly a difference in their work, making them two different commands used in MySQL for their respective purposes.
• The MySQL UNION operator performs to give the distinctive values set in the result after the union of the result rows of SELECT statements, whereas the MySQL UNION ALL operator allows the union to produce the result set from SELECT statements having replica values in the records fetched from the database tables where it is applied.

Syntax

Following is the elementary syntax code for the usage of MySQL UNION ALL operator with the SELECT statements to show the combination of table values having similar field and data types:

SELECT Col_expr1, Col_expr2,…,Col_exprN FROM TableName_A
[WHERE option condition]
UNION ALL
SELECT Col_expr1, Col_expr2,…,Col_exprN FROM TableName_B
[WHERE option condition];

Here, let us explain the parameters listed above:

• The Col_expr1, Col_expr2,…, and Col_exprN are the expressional column values required to be fetched from two or more tables like TableName_A or TableName_B.
• WHERE condition is an optional matching expression for the SELECT statement fields in the tables provided.
• The number of Column fields must be identical for each SELECT query included, and the statement must consist of at least a single table mentioned with the FROM clause.

How does UNION ALL Operator work in MySQL?

• We know that the MySQL UNION ALL operator allows combining the result rows of multiple SELECT queries. Therefore, it fetches the rows from both tables where each statement with the SELECT keyword command must include an equal number of column fields and matching data types.
• This ensures that the resultant rows do not mismatch and produce any MySQL code error. Suppose everything is proper in the UNION ALL query statement. In that case, the result rows are retrieved but comprise duplicate row values between the several SELECT statements used in the command to make a union set of database tables.

Examples to Implement MySQL UNION ALL

Let us evaluate and illustrate some of the examples showing the 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, we will create a table Person with fields Person_ID, Person_Name, and Person_Address and again create a second table Customer with fields CustomerID, CustomerName, Credit_Limit, and 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 samples 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 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 the UNION ALL operator, which provides the resultant table which contains a combination of column values from both tables:

Code:

SELECT Person_Address FROM Person
UNION ALL
SELECT City FROM Customer;

Output:

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

2. UNION ALL operator example to fetch the single field

For this example, let us again create a table named Employee as Person with a field in common with identical data types 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, JoinDate 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:

Here, the ORDER BY 2 option has placed the Person_Name | CustomerNamecolumns by the position in the output set.

Advantages

• Since the operator does not remove duplicates from the result set and pulls every row value from the related tables that fit your query essentials to combine into one table, the MySQL UNION ALL operator works much faster than the MySQL UNION operator.
• While developing reports in the database server, you do not need duplicates in the result. Still, if the UNION is applied, the server must perform additional actions to avoid replicas. So UNION ALL can be a better option, if possible, for performance-based.
• This blocking operator may be useful in some cases to discover 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 a 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 for our query using different SELECT statements compatible in structure to associate the result sets together, we will receive the duplicate table values with it.
• Unlike the UNION operator, the UNION ALL operator query does not filter the result values to omit the distinct union sets as output.

Recommended Articles

We hope that this EDUCBA information on “MySQL UNION ALL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. LIKE In MySQL
2. ANY In MySQL
3. Cursor In MySQL
4. Table In MySQL