How to Use union, except t, and INTERSECT in SQL, unionintersect
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.
Besides, they all process duplicate data in multiple result sets. First, create a test environment, use tempdbcreate 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
The initial results of select * from temptable2 are as follows. 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 ALLselect * from temptable1.
Union
Select * from temptable2select * 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 record is displayed, and the result is as follows: Check the distinct T, which is also de-duplicated, but after removing two or more duplicate data sets, only the data in the first result set will be retained. select * from temptable1
Except
Select * from temptable2 is actually used to query Table A to check whether the data in Table A exists in Table B. If yes, delete the data and INTERSECT is better understood, that is, query the union of the two result sets, with the above data, only one query result is available, namely {id: 1, price: 3}