What is Difference between Union and Union all? | Union vs Union all

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

Second table

Table Name:Employee_Cognos

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:

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

Second table

Table Name:Employee_Cognos

Query:

Select Employee_Num,Employee_name,Department,Salary from Employee_OBIEE;

Union All

Select Employee_Num,Employee_name,Department,Salary from Employee_COGNOS;

Result:

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 :

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.

HOME