Operations for collections in SQL Server (set, difference, intersection) learning

First, let's do the test data.

1. Create test data

--Create a People tableCreate TablePerson1 (Uidint Identity(1,1)Primary Key, Namevarchar( -) not NULL)--Create a People table 2--Create TablePerson2 (Uidint Identity(1,1)Primary Key, Namevarchar( -) not NULL)--inserting data--Insert  intoPerson1Values('Caocao'),('Guo Jia'),('Code Wei'),('Sun Quan'),('Zhou Yu')Insert  intoPerson2Values('Liu Bei'),('Guan Yu'),('Zhang Fei'),('Sun Quan'),('Zhou Yu')--querying data--Select *  fromPerson1Select *  fromPerson2

2, the test data query results are as follows

1) data from table 1 and table 2

3. The set, difference set and intersection in a collection

3.1. Union

1) Definition of the Set

Union can connect two or more result sets to form a "set". All the records of the child result set are grouped together to form a new result set.

2) Restriction conditions
2.1) The child result set must have the same structure.
2.2) The number of columns in the word result set must be the same.
2.3) The data type corresponding to the child result set must be compatible.
2.4) Each child result set cannot contain the ORDER BY and COMPUTE clauses

3) use of the set

-- 1.  Union-----1) Remove duplicate set-- Select  from Person1 Union Select  from Person2 -- 2) Do not remove the set of duplicates-- Select  from Person1 Union  All Select  from Person2

4) Results of the query

3.2. Difference set (except)

1) Definition of difference set

Except can connect two or more result sets to form a "difference set". Returns records that are already in the results collection on the left and those that are not in the right result set.

2) Restriction conditions

2.1) The child result set must have the same structure.

2.2) The number of columns in the child result set must be the same

2.3) The data type corresponding to the child result set must be compatible.

2.4) Each child result set cannot contain an order BY and a COMPUTE clause.

3) Use of the difference set

-- Except Difference Set--- Select  from  exceptselect from Person2

4) Results of the query

3.3. Intersection (InterSect)

1) Definition of intersection

Intersect can connect two or more result sets to form an "intersection." Returns records from the left and right result set

2) Restriction conditions

2.1) The child result set must have the same structure.

2.2) The number of columns in the child result set must be the same

2.3) The data type corresponding to the child result set must be compatible.

2.4) Each child result set cannot contain an order BY and a COMPUTE clause.

3) use of the intersection

-- InterSect Intersection-- Select *  from  InterSectSelect* from Person2

4) Results of the query

Reference URL:

Http://www.cnblogs.com/kissdodog/archive/2013/06/24/3152743.html

Operations for collections in SQL Server (set, difference, intersection) learning