SQL Set Operators combines the result of 2 queries or components on to the single result. The queries containing the different set operators like union, union all, intersection minus are simply called as Compound Query. SQL set operators used to get meaningful data from 2 or more different tables. In real world scenarios set operators are very useful in reporting, analytics, creating data-warehouse. I have explained about Minus and intersect operator in other article. This article will give you the basic idea about sql union vs union all with real industrial examples. First we need to know about what exactly the SQL Set Operators means and brief idea about set operators. Then our next task is to understand the sql union vs union all. In this article we need to explain more on difference between union and union all with examples.
Set operators are useful in reporting, analytics to combine the data from 2 or more tables
Difference Between Union and Union all
Union Operator :
Union Operator combines the result of 2 or more tables and fetches the results of two select statements.Union operator eliminates the duplicates from the table and fetches the result.For each duplicate row in table only one row is displayed in the result.By considering the performance of SQL using union is not preferable option but if there is situation where user wants to remove the duplicate data from two or more table the use of Union is preferable.
CLICK HERE TO GET INFORMATION OF JOINS IN SQL
Syntax Of Union:
Select column1…column n from table1;
union
Select column1…column n from table2;
Example/ Real Life Scenario :
Kindly select employees from 2 different employee tables eliminating duplicates
Suppose following is first table:
Table name:
Employee_OBIEE
| Employee_num | Employee_name | Department | Salary |
| 1 | Amit | OBIEE | 680000 |
| 2 | Rohan | OBIEE | 550000 |
| 3 | Rohit | COGNOS | 430000 |
Second table
Table Name:Employee_Cognos
| Employee_num | Employee_name | Department | Salary |
| 1 | Pradnya | Cognos | 522000 |
| 2 | Mohit | Cognos | 471100 |
| 3 | Rohit | COGNOS | 430000 |
Query:
Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;
Union
Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;
Here Duplicate record has been removed for Employee name Rohit and output is as follows:
| Employee_num | Employee_name | Department | Salary |
| 1 | Amit | OBIEE | 680000 |
| 2 | Rohan | OBIEE | 550000 |
| 3 | Rohit | COGNOS | 430000 |
| 1 | Pradnya | COGNOS | 522000 |
| 2 | Mohit | COGNOS | 471100 |
These are some examples of Union Operator but our main motive is to check the sql union vs union all operator with examples.But to check this difference user needs to know about Union all Operator with examples also.
Union ALL Operator :
Union ALL Operator combines the result of 2 or more tables and fetches the results of two or more select statements.Union all operator does not eliminate duplicate values.It shows duplicate records also.By considering the performance of SQL using union all is preferable option because it does not check the duplicate values so no sorting required at the time of fetching the records.Union all operator is most widely used operator in reporting purpose where user needs to fetch the records from different tables.
Syntax:
Select column1…column n from table1;
union all
Select column1…column n from table2;
Example/Real Life Scenario:
Kindly select employees from 2 different employee tables without eliminating duplicates
Suppose following is first table:
Table name:
Employee_OBIEE
| Employee_num | Employee_name | Department | Salary |
| 1 | Amit | OBIEE | 680000 |
| 2 | Rohan | OBIEE | 550000 |
| 3 | Rohit | COGNOS | 430000 |
Second table
Table Name:Employee_Cognos
| Employee_num | Employee_name | Department | Salary |
| 1 | Pradnya | Cognos | 522000 |
| 2 | Mohit | Cognos | 471100 |
| 3 | Rohit | COGNOS | 430000 |
Query:
Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;
Union All
Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;
Result:
| Employee_num | Employee_name | Department | Salary |
| 1 | Amit | OBIEE | 680000 |
| 2 | Rohan | OBIEE | 550000 |
| 3 | Rohit | COGNOS | 430000 |
| 1 | Pradnya | COGNOS | 522000 |
| 2 | Mohit | COGNOS | 471100 |
| 3 | Rohit | COGNOS | 430000 |
This sections gives you the brief idea about union all operator. Union all operator is faster than union operator because sorting is not there in union all operator. The following is sql union vs union all operator.
Difference between Union and Union all in tabular format :
| Union | Union ALL |
| 1.Union Set operator is used to fetch the records from 2 different tables which eliminates the duplicate records | 1.Union all Set operator is used to fetch the records from 2 different tables which does not eliminates the duplicate records |
| 2.Syntax:
Select col1,col2…from table1; Union Select col1,col2…from table2; |
2.Syntax:
Select col1,col2…from table1; Union all Select col1,col2…from table2; |
| 3.For Performance tuning Union operator is not preferable as it takes time to eliminate duplicate records | 3.Union all is preferable operator in Performance tuning. |
These are some important sql union vs union all operator. Hope this article will useful to every programmers. Hope you like this article of union and union all difference. If you like this article or if you have any issues with the same kindly comment in comments section.