SQL Server: Union, except T, intersect)

SQL Server has several processing operations on the result set. It is worth explaining.

1. Union (Union, Union all)

This is simple. It combines two result sets horizontally. For example

Select * from

Union

Select * from B

[Note] Union deletes duplicate values. That is to say, duplicate rows in A and B will only appear once, while union all will keep duplicate rows.

 

2. Variance t)

It is the unique part of the two sets. For example

Select * from

Except

Select * from B

This means that no row exists in Table A of table B.

 

3. Intersection (InterSect)

Is the common part of the two sets. For example

Select * from

Intersect

Select * from B

This means that records appear in both A and B

 

By the author: Chen xizhang in 2009/6/26 18:31:02 published in: http://www.cnblogs.com/chenxizhang/
This article is copyrighted by the author and can be reproduced. However, this statement must be retained without the author's consent, and the original article is clearly connected on the article page. Otherwise, the legal liability will be retained.
For more blog articles, as well as the author's complete comments on Blog references and the policy of cooperation, please refer to the following site: Chen xizhang's blog Center
This article uses the blog sync and management system to automatically synchronize data from Chen xizhang @ blog Park at 18:31:04 on. Http://www.cnblogs.com/chenxizhang/archive/2009/06/26/1511889.html, published on 10:31:00.

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

 

How to Use union, except T, and intersect:

 

The Union, except T, and intersect in SQL are demonstrated below:

There is a reason to put these three together, because they all operate on two or more result sets, and these result sets have the following restrictions:

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

And they all process duplicate data in multiple result sets.

First, create a test environment.

 

Use tempdb

Create Table temptable1 (ID int Primary Key Identity, price INT)
Create Table temptable2 (ID int Primary Key Identity, price INT)
Insert into temptable1 select 3 Union all select 1 Union all select 2 Union all select 3
Insert into temptable2 select 3 Union all select 4 union all select 1 Union all select 2

Select * From temptable1
Select * From temptable2

 

The initial results of the two tables are as follows:

 

The number of columns in two simple tables is the same as that in the column sequence. As long as the data is completely consistent, they can be said to be duplicate data, so that the above three computing words will be valid.

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 keyword "all" is to fully integrate two result sets, and removing "all" is based on the previous result sets. Therefore, in the first query, {ID: 1, price: 3} only one entry is displayed. The result is as follows:

Let's take a look at the distinct T, which is also de-duplicated. However, after removing duplicate data from two or more sets, only the data in the first result set is retained.

Select * From temptable1
Except
Select * From temptable2

 

In fact, it is also used to query Table A to check whether the data in Table A exists in Table B. If so, delete the data.

Intersect is better understood, that is, to query the union of the two result sets. With the above data, only one result is queried, that is, {ID: 1, price: 3 }, the code and result diagram are not provided here.