Note-microsoft SQL Server 2008 Technical Insider: T-SQL Language Foundation-06 set operation

T-SQL supports 3 sets of operations: Union, intersection (INTERSECT), and Difference set (EXCEPT). The two queries involved in a set operation cannot contain an ORDER BY clause.

UNION all set operation

UNION all does not compare rows, nor does it delete duplicate rows. Assuming that the query Query1 returns m rows, and the query Query2 returns n rows, the Query1 UNION all Query2 return (m+n) rows.

SELECT  from HR. EmployeesUNIONallSELECT from Sales.customers;

UNION DISTINCT set operation

UNION distinct deletes duplicate rows.

SELECT  from HR. EmployeesUNIONSELECT from Sales.customers;

INTERSECT DISTINCT set operation

SELECT  from HR. EmployeesINTERSECTSELECT from Sales.customers;

Note that when a set operation compares rows, two null values are considered equal.

INTERSECT all set operation

SQL Server does not support the built-in intersect all operation and requires an alternative solution to implement intersect all. You can use Row_number to implement this requirement.

SELECTrow_number () Over(PARTITION bycountry, region, CityORDER      by(SELECT 0)) asrownum, country, region, City fromHR. EmployeesINTERSECTSELECTrow_number () Over(PARTITION bycountry, region, CityORDER      by(SELECT 0), country, region, City fromSales.customers;

Execution Result:

Note In the above SQL, using order by in the over clause of the sort function (select< constant >) is the order that tells SQL Server not to care about rows. If you want the returned result to have no line number, you can define a table expression based on the query, such as:

 withIntersect_all as(  SELECTrow_number () Over(PARTITION bycountry, region, CityORDER      by(SELECT 0)) asrownum, country, region, City fromHR. EmployeesINTERSECT  SELECTrow_number () Over(PARTITION bycountry, region, CityORDER      by(SELECT 0), country, region, City fromsales.customers)SELECTcountry, region, City fromIntersect_all;

EXCEPT DISTINCT set operation

SELECT  from HR. EmployeesEXCEPTSELECT from Sales.customers;

Note that in the except set operation, swapping the operation positions of two sets makes the results of the operation different.

EXCEPT Alternative solution for all operations

 withExcept_all as(  SELECTrow_number () Over(PARTITION bycountry, region, CityORDER      by(SELECT 0)) asrownum, country, region, City fromHR. EmployeesEXCEPT  SELECTrow_number () Over(PARTITION bycountry, region, CityORDER      by(SELECT 0), country, region, City fromsales.customers)SELECTcountry, region, City fromExcept_all;

Note-microsoft SQL Server 2008 Technical Insider: T-SQL Language Foundation-06 set operation