Union Vs. Union all

SQL Language Elements, Identifiers, Data Types, Operators, Comments, Query Expressions, Search Conditions, Expressions, Literals and Functions Etc,
Post Reply
AyubHussain
Posts: 142
Joined: Fri Nov 23, 2018 2:48 am

Union Vs. Union all

Post by AyubHussain » Tue Dec 04, 2018 6:09 am

What is difference between Union and Union all Operators?

dsusmitha
Posts: 62
Joined: Fri Nov 23, 2018 2:51 am

Re: Union Vs. Union all

Post by dsusmitha » Tue Dec 04, 2018 6:11 am

Union :

1.Union Set operator is used to fetch the records from 2 different tables which eliminates the duplicate records
2.Syntax:Select col1,col2…from table1;
Union
Select col1,col2…from table2
3.For Performance tuning Union operator is not preferable as it takes time to eliminate duplicate records

Union ALL :

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;
3.Union all is preferable operator in Performance tuning.

srini
Posts: 85
Joined: Thu Jan 03, 2019 3:58 am

Re: Union Vs. Union all

Post by srini » Mon Jan 21, 2019 12:05 pm

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 the table, only one row is displayed in the result. By considering the performance of SQL using union is not a preferable option but if there is a situation where a user wants to remove the duplicate data from two or more table the use of Union is preferable.

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 a 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 a user needs to fetch the records from different tables.

chowdarybabu
Posts: 21
Joined: Thu Jan 03, 2019 3:52 am

Re: Union Vs. Union all

Post by chowdarybabu » Fri Jan 25, 2019 10:34 am

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.
There is a performance hit when using UNION instead of UNION ALL, since the database server must do additional work to remove the duplicate rows, but usually you do not want the duplicates (especially when developing reports).

Post Reply

Return to “SQL Language Elements”