Union and Union All:
UNION is one of the set operations that allow you to combine results of two SELECT statements into a single result set which includes all the rows that belong to the SELECT statements in the union.Both work when data type of selected column and also column should be same on both table
Syntax of union:-
Select c1,c2,c3,c4
from table1
union
select c1,c2,c3,c4
from table2
-- ist query return 179
select FirstName,MiddleName,LastName,EmailAddress,Phone from [dbo].[DimEmployee]
where DepartmentName='Production'
union
--2nd query returns 106
select FirstName,MiddleName,LastName,EmailAddress,Phone from [dbo].[DimCustomer]
where GeographyKey=31
It doesn't allow duplicate row
*** All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Syntax of union all: - union all at place of union
UNION vs. UNION ALL
By default, the UNION operator removes all duplicate rows from the result sets. However, if you want to retain the duplicate rows, you need to specify the ALL keyword is explicitly as shown below:
UNION vs. JOIN
The join such as INNER JOIN or LEFT JOIN combines columns from two tables while the UNION combines rows from two queries.
Intersect:
INTERSECT combines result sets of two or more queries and returns distinct rows that are output by both queries.
Intersect does all columns.
- When using INTERSECT operator the number and the order of the columns must be the same in all queries as well data type must be compatible.
- Use an intersect operator to returns rows that are in common between two tables; it returns unique rows from both the left and right query.
- This query is useful when you want to find results that are in common between two queries.
EXCEPT operator
The SQL Server EXCEPT compares the result sets of two queries and returns the distinct rows from the first query that are not output by the second query. In other words, the EXCEPT subtracts the result set of a query from another.
select ProductKey from DimProduct
except
select ProductKey from FactInternetSales
The following are the rules for combining the result sets of two queries in the above syntax:
- The number and order of columns must be the same in both queries.
- The data types of the corresponding columns must be the same or compatible.
