Difference between Union and Union all | SQL union vs union all

SQL 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.

Set operators are useful in reporting,analytics to combine the data from 2 or more tables

 

Union Operator :sql union vs union all

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.

HOME

Leave a Reply