- MS Sql Server
- 906
- February-02-2018
- by Rizwan Saqib
Union & Union ALL
1.Definition of Union
In UNION clause combines the results of two SQL queries into a single table of all matching rows. The two queries must result in the same number of columns and compatible data types in order to unite. Any duplicate records are automatically removed unless UNION ALL is used.
For Example
Two tables
1.Employee
2.Designation
Select Employee_ID,Employee_Name From Employee
Union
Select Designation_ID,Designation from Designation
(If there is Multiple name and ID union give only Non duplicate values)
2.Definition of Union All
The SQL Server UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query and it does not remove duplicate rows between the various SELECT statements.
For Example
Two tables
3.Employee
4.Designation
Select Employee_ID,Employee_Name From Employee
Union ALL
Select Designation_ID,Designation from Designation
Union all give all duplicate and non duplicate data
3.Intersect
Intersect operation is used to combine two SELECT statements, but it only returns the records which are common from both SELECT statements. In case of Intersect the number of columns and data type must be same. My SQL does not support INTERSECT operator.
For Example
Two tables
5.Employee
6.Designation
Select Employee_ID,Employee_Name From Employee
INTERSECT
Select Designation_ID,Designation from Designation
Intersect gives only Common data from both Tables
4.Minus
Minus operation combines result of two Select statements and return only those result which belongs to first set of result.
Difference Between Union and Union all
Union
1.Union can not Give Duplicate Value
2.Union is slower than union all
Union All
1.Union all give Duplicate Values
2.Union all faster than Union
