MySQL UNION ALL

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, MySQL UNION operator it is also a useful command in MySQL database to combine more than two of the output set provided by the use of SELECT queries. But there is slightly a difference in their work which makes them two different commands used in MySQL for their respective purposes.
• The MySQL UNION operator performs to give the distinctive values of set in the result after the union of the result rows of SELECT statements whereas the MySQL UNION ALL operator allows the union producing 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 shows 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,…, Col_exprN is the expressional column values that are required to be fetched from two or more tables likeTableName_A or TableName_B.
• WHERE condition is an optional matching expression to be used 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?

• As we come to know that the MySQL UNION ALL operator allows combining the result rows of multiple SELECT queries. Therefore, it fetches the rows from both the tables where each statement with SELECT keyword command must include an identical number of column fields along with the matching data types.
• This is because to assure that the resultant rows do not mismatch and produce any MySQL code error. If everything is proper in the UNION ALL query statement then, the result rows are retrieved but it comprises duplicate row values between the several SELECT statements used in the command to make union set of database tables.

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 commonwith 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, 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 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.

Recommended Articles

This is a guide to MySQL UNION ALL. Here we discuss an introduction, syntax, how does it works with examples to implement with proper coding. You can also go through our other related articles to learn more –

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