Several advanced query operators in SQL Server (UNION, EXCEPT T, and INTERSECT)

Here we mainly demonstrate three arithmetic words, UNION, except t, and INTERSECT. There is a reason to put them together, because they all operate on two or more result sets, 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:

 

It is very simple for two tables, with the same columns and column order. one piece of data in the data is the same, and the data here is the same, including the primary key. The primary key here is the ID column, so the insertion sequence is the same. If it is not the ID column, it is random, as long as the data is completely consistent, it can be said that they are 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. You can try it!