The following articles have been published [SqlServer Series]:

• [SqlServer Series] SQLSERVER Installation Tutorial 
• [SqlServer Series] Three Database Paradigms
• [SqlServer Series] Form Query
• [SqlServer Series] Table Connections
• [SqlServer Series] Subquery
• [SqlServer Series] Turn on Sqlserver remote access

This article goes on to write Set Operations (SqlServer Series). The main contents are: 1. Union Operations (UNION), Intersection Operations (INTERSECT), Difference Operations (EXCEPT) 2, Set Operations Priority 3, Avoiding Unsupported Logical Query Processing

1.2 A brief overview of the three operations

1.3 Test sample tables and SQL for this chapter

Business Scenarios

There are two tables, one is the Employee Form (Employee ID, Employee Name, Position, Degree, Origin, Phone) and the other is the Sales Form (Sales ID, Employee ID, Employee Name, Position, Degree, Sales)

(1) Create a collection DB:WJM_CollectDemo

1 IF DB_ID('WJM_CollectDemo') IS NOT NULL
2    DROP DATABASE WJM_CollectDemo
3    GO
4    CREATE DATABASE WJM_CollectDemo

(2) Create employee table and initialize

 1 USE WJM_CollecDemo
 2 
 3 --CREATE TABLE(Employees) AND INITIAL
 4 CREATE TABLE Employees
 5 (
 6   empID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 7   empName VARCHAR(50),
 8   position VARCHAR(50),
 9   degree VARCHAR(50),
10   jiGuan VARCHAR(50),
11   tel VARCHAR(50),
12 )
13 
14 INSERT INTO Employees VALUES
15 ('Zhang San','sales manager','Undergraduate','Shanghai','021-298989'),
16 ('Li Si','Sale','Undergraduate','Beijing','010-298181'),
17 ('Li Ming','Sale','','Shenzhen','0755-698988'),
18 ('Wang Hua','Sale','Undergraduate','Hangzhou','0571-593132')

Execute Query Statement

1  SELECT *
2  FROM Employees

The results of the query are:

(3) Create sales table and initialize

 1 USE WJM_CollectDemo
 2 
 3 --CREATE TABLE(Sales) AND INITIAL
 4 CREATE TABLE Sales
 5 (
 6   salesID INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
 7   empID INT,
 8   empName VARCHAR(50),
 9   position VARCHAR(50),
10   degree VARCHAR(50),
11   SaleCount VARCHAR(100)
12 )
13 
14 INSERT INTO Sales VALUES
15 ('1','Zhang San','manager','Undergraduate','5000w'),
16 ('3','Li Ming','Sale','','100w'),
17 ('4','Wang Hua','Sale','Undergraduate','1500w'),
18 ('','Zhang Tao','External Sales','master','2000w')

Execute Query Statement

1  SELECT *
2  FROM Sales

Query Results

2.1 union operation (UNION)

(1) UNION ALL (do not delete duplicate rows)

 Code:

1 SELECT empID,empName,position,degree
2 FROM Employees
3 UNION ALL
4 SELECT empID,empName,position,degree
5 FROM Sales

Query results:

Result analysis:

The result is a simple combination of the first query result set and the second query result set, retaining duplicate rows.

(2) UNION (implicit DINSTINCT, delete duplicate rows)

 Code:

1 SELECT empID,empName,position,degree
2 FROM Employees
3 UNION  
4 SELECT empID,empName,position,degree
5 FROM Sales

Query results:

Result analysis:

UNION (implicit DISTINCT) is comparable to using UNION ALL as an intermediate result and then filtering out duplicate rows through DISTINCT on its basis.

(3) Summary

A. Union is a simple combination of two query result sets;
b. Multiple sets refer to duplicate rows in a set, and single sets refer to no duplicate rows in a set.
c.UNION ALL is generally a multiset, UNION (UNION DISTINCT) is generally a set;
d. Symmetry, that is, no matter which query is in front, the result of the query is the same;
e. When null value comparison is made, it is considered equal, while inner connection, EXISTS predicate in null comparison results in UNKNOWN;

2.2 Intersection (INTERSECT)

 Code:

1 SELECT empID,empName,position,degree
2 FROM Employees
3 INTERSECT  
4 SELECT empID,empName,position,degree
5 FROM Sales

Query results:

Result analysis:

Intersection is the common part of the first and second query result sets

Summary

a. Intersection is the common part of the first query result set and the second query result set;
b. When null value comparison is made, it is considered equal, while inner connection, EXISTS predicate in null comparison results in UNKNOWN;
c. There are two types of INTERSECT and INTETSECT ALL (not implemented in the SQL 2008 version);
d. Symmetry, that is, no matter which query is in front, the result of the query is the same;

2.3 Difference Set

Code:

1 SELECT empID,empName,position,degree
2 FROM Employees
3 EXCEPT  
4 SELECT empID,empName,position,degree
5 FROM Sales

Query results:

Result analysis:

The difference set operation operates on the result set of two input queries and returns all rows that appear in the first result set but not in the second result set.

Summary:

A. The EXCEPT operation operates on the result set of two input queries and returns all rows that appear in the first result set but not in the second result set.
b. Not symmetrical;
c. There are two types of EXCEPT and EXCEPT ALL (not implemented in the SQL 2008 version);

SQL defines the priority between set operations.INTERSECT priority is higher than UNION and EXCEPT, UNION and EXCEPT priority are the same.Execution order is from left to right.

CODE:

1 SELECT empID,empName,position,degree
2 FROM Employees
3 UNION 
4 SELECT empID,empName,position,degree
5 FROM Sales
6 INTERSECT
7 SELECT empID,empName,position,degree
8 FROM Employees

Query results:

Result analysis:

Summary:

a.INTERSECT has the highest priority, UNION has the same priority as EXCEPT;
b. Execute in the order of execution from left=>right;

Omitted (explained in the advanced section)

[01] Microsoft SqlServer 2008 Technical Insider: T-SQL Language Foundation

[02] Microsoft SqlServer 2008 Technical Insider: T-SQL Query