Union Vs. Union all
-
AyubHussain
- Posts: 142
- Joined: Fri Nov 23, 2018 2:48 am
Union Vs. Union all
What is difference between Union and Union all Operators?
Re: Union Vs. Union all
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.
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.
Re: Union Vs. 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 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.
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
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).
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).