Blog

SQL Difference between Union and Union All

SQL

SQL Difference between Union and Union All

The UNION operator is used to combine the result-set of two or more SELECT statements.

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

Difference between union and union all is as follows

UNION

Removes any duplicate records and keeps only the unique records

UNION ALL

Keeps all of the records from each of the original data sets, including the duplicate records

So in the above diagram, we can see that there is a duplicate values between two tables. Now let us use the UNION command to merge the two tables.

				
					SELECT FirstName, LastName, Age FROM EMPLOYEE
UNION
SELECT FirstName, LastName, Age FROM STUDENT
				
			

We will get the result as follows

In the above result we can see that only the distinct rows have been fetched.

Now, let us run the UNION ALL operator as follows

				
					SELECT FirstName, LastName, Age FROM EMPLOYEE
UNION ALL
SELECT FirstName, LastName, Age FROM STUDENT
				
			

We will now get the following result

In the above result we can see that the duplicate value is returned.

Please comment and share if you like this post and tell us about how we can enhance our posts. Thanks.

Subhajit