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:
NOTE: We must have to know that Union and Join are different.
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 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:
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.