SQL Difference between Union and Union All
January 2, 2023 2023-01-02 20:06SQL Difference between Union and Union All
Subhajit The UNION operator is used to combine the result-set of two or more SELECT statements.
- Every
SELECTstatement withinUNIONmust have the same number of columns. - The columns must also have similar data types.
- The columns in every
SELECTstatement 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.
