Difference between Union and Union All
UNION and UNION ALL are the two most essential SQL operators used in the database for combining the result set from multiple tables. These operators allow us to use multiple SELECT queries, retrieve the desired results, and then combine them into a final output. In this article, we are going to see how they are different from each other. Before making a comparison, we will discuss in brief about these operators.
What is a Union operator?
Union operator in MySQL allows us to combine two or more results from multiple SELECT queries into a single result set. It has a default feature to remove the duplicate rows from the tables. This operator syntax always uses the column’s name in the first SELECT statement to be the column names of the output.
MySQL Union must follow these basic rules:
- The number and order of the columns should be the same in all queries.
- The corresponding columns position of each select query must have a compatible data type.
- The column name selected in the different SELECT queries must be in the same order.
- The column name of the first SELECT query will be the column names of the output.
NOTE: We must have to know that Union and Join are different.
- JOIN combines data from multiple different tables, whereas UNION combines data from multiple similar tables.
- JOIN appends the output horizontally, whereas UNION combines the result set vertically.
The below visual representation explains it more clearly:
To read more information about the Union operator, click here.
What is Union All?
The UNION ALL operator combines two or more results from multiple SELECT queries and returns all records into a single result set. It does not remove the duplicate rows from the output of the SELECT statements.
We can understand it with the following visual representation.
Union vs. Union All Operator
The following comparison table explains their main differences in a quick manner:
| UNION | UNION ALL |
|---|---|
| It combines the result set from multiple tables and returns distinct records into a single result set. | It combines the result set from multiple tables and returns all records into a single result set. |
| Following is the basic syntax of UNION operator: SELECT column_list FROM table1 UNION SELECT column_list FROM table2; | Following is the basic syntax of UNION ALL operator: SELECT column_list FROM table1 UNION ALL SELECT column_list FROM table2; |
| It has a default feature to eliminate the duplicate rows from the output. | It has no feature to eliminate the duplicate rows from the output. |
| Its performance is slow because it takes time to find and then remove duplicate records. | Its performance is fast because it does not eliminate the duplicate rows. |
| Most database users prefer to use this operator. | Most database users do not prefer to use this operator. |
Union and Union All Example
Let us understand the differences between Union and Union All operators through an example. Suppose we have a table named “Student” and “Student2” that contains the following data:
Table: Student
Table: Student2
Following SQL statement returns the distinct name of cities from both tables using the UNION query:
After executing the above statement, we will get the below output because the Union operator returns only the distinct values.
Following SQL statement returns all cities name including duplicates from both tables using the UNION ALL query:
After executing the above statement, we will get the below output because the Union All operator returns whole records without eliminating distinct values.
