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

About admin 138 Articles
Oracle Consultant with Vast Experience in Oracle BI and SQL PLSQL Development

16 Trackbacks / Pingbacks

  1. Interview Questions For Oracle | SQL interview Questions
  2. Difference between Truncate and Delete, Drop in SQL | Truncate Delete and Drop Commands in SQL | SQL Difference between Truncate,Delete and Drop Commands | Real Life Examples of Truncate Delete and Drop Command
  3. Interview Questions for TCS | SQL Interview Questions for TCS | Interview Questions Asked in TCS | TCS Interview Questions
  4. Interview Questions for HCL | SQL Interview Questions for HCL | Interview Questions Asked in HCL | HCL Interview Questions | 20 Most Important SQL Interview Questions for HCL
  5. Oracle Partitioning | Table Partitioning in Oracle | Partitioning in SQL | How Partitioning helps improving Performance | Table Partitioning with Examples | Partitioning on existing table
  6. Operators in SQL | SQL Operators | Arithmetic Operators | Logical Operators | Comparison Operators | Negation Operators | Operators in SQL with Examples
  7. Pivot in SQL with Example | Pivot Clause | Pivot and unpivot in SQL | Pivot in Oracle | SQL Pivot with Real Life Example
  8. SQL Functions List,Functions in SQL,SQL Functions example,Aggregate functions,scalar functions,Aggregate functions in sql,scalar functions in sql,functions
  9. Sed Command in Unix with examples | Stream Editor in Unix | Pipe Command with Examples | Head and Tail command with examples
  10. Grep Command in unix | Grep Command with Examples | Grep Command with Options | Search String in Unix File | Practical Examples of Grep command | fgrep command with example | egrep command with example
  11. Process Commands in Unix | Unix Filter commands | Touch command with example | ps command with examples | Kill command with examples
  12. Unix Directory Commands | Create Directory in Unix | Mkdir Command with Example | PWD command with example | Rmdir command to remove directory | Cd command to change directory
  13. SQL Constraints | Constraints in SQL | Primary Key in SQL | Foreign Key in SQL | Check Constraints in SQL | Null Value
  14. OBIEE
  15. Database Normalization Examples | Normalization in Database with Examples | Database Normalization with Real Life Examples | Designing Normalized Database
  16. Interview Questions for Infosys | SQL Interview Questions for Infosys | Interview Questions Asked in Infosys | Infosys Interview Questions

Leave a Reply

Your email address will not be published.


*