SQL SERVER: Merging related Operations (Union,except,intersect)-Reproduced

There are several processes in SQL Server for the result set, which is worth explaining

1. Set (Union,union All)

This is simple, merging the two result set levels together. For example

SELECT * from A

UNION

SELECT * from B

Note: The union removes duplicate values, that is, a repeating row of data in A or B, which eventually appears only once, and union all preserves duplicate rows.

2. Differences (Except)

Is the two parts that are not duplicated in a set. For example

SELECT * from A

EXCEPT

SELECT * from B

This means that the rows of a table that do not appear in table B.

"Note" Except also removes duplicate values, that is, duplicate rows of data in a, which will only occur once, and except does not support the all statement

3. Intersection (INTERSECT)

is a common part of the two clusters. For example

SELECT * from A

INTERSECT

SELECT * from B

This means that the records that appear at the same time in A and b

"Note" Intersect also removes duplicate values, that is, duplicate rows of data in A and B at the same time, and eventually only occurs once, and intersect does not support the all statement

This article was published by Chen Xizhang on 2009/6/26 18:31:02 in: http://www.cnblogs.com/chenxizhang/This article is copyrighted by the author, can be reproduced, but without the consent of the author must retain this statement, and in the article page obvious location to the original link , otherwise reserves the right to pursue legal liability. For more blog posts, as well as the author's full statement of the blog references and the policy of cooperation, please refer to the following sites: Chen Xizhang's Blog Center This article is the use of blog synchronization and management system automatically on 2009/6/26 18:31:04 from the Chen Xizhang @ Blog Park sync come over. Original address: http://www.cnblogs.com/chenxizhang/archive/2009/06/26/1511889.html, posted on 2009/6/26 10:31:00.

This article from Csdn Blog, reproduced please indicate the source: http://blog.csdn.net/chen_xizhang/archive/2009/06/26/4301621.aspx

UNION, EXCEPT, and intersect use methods :

The Union in SQL, EXCEPT, and intersect do the following:

These three are justified because they all operate on two or more result sets, and these result sets have the following limitations:

• The number of columns and the order of the columns must be the same in all queries.
• The data type must be compatible.

And they are all dealing with the problem of repeating data in multiple result sets

Create a test environment first

 UsetempdbCreate TableTempTable1 (IDint Primary Key Identity, Priceint)Create TableTempTable2 (IDint Primary Key Identity, Priceint)Insert  intoTempTable1Select 3 Union  All Select 1 Union  All Select 2 Union  All Select 3 Insert  intoTempTable2Select 3 Union  All Select 4 Union  All Select 1 Union  All Select 2Select *  fromTemptable1Select *  fromTemptable2

The initial results for two tables are as follows

A very simple two table with the same number of columns and column order. As long as the data is fully consistent, it can be said that they are duplicate data, so that the above 3 operating words will be effective.

Let's take a look at Union and union all.

Select *  from Temptable1 Union Select *  from Temptable2 Select *  from Temptable1 Union  All Select *  from Temptable2

The ALL keyword is fully integrated with two result sets, without all being heavy on the previous basis, so {id:1, price:3} in the first query will only display one, the result is as follows:

Looking at except is also going to be heavy, but it only retains data from the first result set after removing duplicate data from two or more sets

Select *  from Temptable1 except Select *  from Temptable2

In fact, the query table A, see if the data in table a exists in table B, if there is, then delete

and intersect better understand, is to query two result sets of the set, using the above data, the results of the query only one, is {id:1, price:3}, here does not give the code and the results of the diagram

SQL SERVER: Merging related Operations (Union,except,intersect)-Reproduced