Figure 1 If there are duplicate rows in the UNION, they will be removed, leaving only one
1. Introduction
The except and INTERSECT operators allow you to compare the results of two or more SELECT statements and return distinct values.
2. Differences
The except operator returns all distinct values returned by the query to the left of the except operator and not included in the value returned by the query on the right.
Intersect returns all distinct values returned by the query on the left and right side of the Intersect operator.
3. Precautions
(1). Result sets that are compared using except or intersect must have the same structure. They must have the same number of columns, and the data type of the corresponding result set column must be compatible
(2). The Intersect operator takes precedence over except
(3). SELECT into must be the first query in a statement that contains the Intersect or except operator to create a table that holds the final result set
(4). The column name or alias in the ORDER by clause must refer to the column name returned by the left-side query
4. Examples:
--Create 3 tables and insert data separately
CREATE TABLE TableA (col1 int)
INSERT INTO TableA Select 1
INSERT INTO TableA Select 1
INSERT INTO TableA Select 2
Insert INTO TableA Select 3
INSERT INTO TableA Select 4
INSERT INTO TableA Select 4
INSERT INTO TableA Select 5
INSERT INTO TableA Select null
INSERT INTO TableA Select null
CREATE TABLE TableB (col2 int)
INSERT INTO TableB Select null
INSERT INTO TableB Select 1
INSERT INTO TableB Select 2
Insert INTO TableB Select 3
CREATE TABLE TableC (col3 int)
INSERT INTO TableC Select 1
INSERT INTO TableC Select 5
INSERT INTO TableC Select 6
--Using except
---Find out that the col1 column of the TableA table does not have all the distinct values of the TableC table col1 column
SELECT col1 from TableA
EXCEPT
SELECT col3 from Tablec
The results are as follows:
Col1
-----------
Null
2
3
4
--sql version 2000, using not exists to implement the function of the except SELECT col1
From TableA as a
Where NOT EXISTS (SELECT col3 from Tablec where a.col1=col3)
GROUP BY col1
--sql 2000,not in is not getting the above results
--Null value indicates unknown value. A null value differs from a blank or 0 value. There are two equal null values.
--Compares two null values or returns an unknown value compared to any other value, because each null value is
Unknown.
--all null values returned after using in or not in comparison will return unknown. --using null values with in or not will produce unexpected results. SELECT col1
From TableA
where col1 not in (SELECT col3 from Tablec)
GROUP BY col1
The results are as follows:
Col1
-----------
2
3
4
The--intersect operator takes precedence over except-the operation steps are: intersect of the first operation TableB and TableC, and TableA operations except
SELECT col1 from TableA EXCEPT
SELECT col2 from TableB INTERSECT
SELECT col3 from TableC
The results are as follows:
Col1
-----------
Null
2
3
4
5
Application of--select into
--select into must be the first query in the statement-I remember the use of the SELECT into and union operators is also a rule
SELECT col1
Into #tem
From TableA
EXCEPT
SELECT col3
From Tablec
SELECT * FROM #tem drop table #tem
The results are as follows:
Col1
-----------NULL
2
3
4
[explains the]sql except and intersect operators (compared to the results of two or more SELECT statements and goes to distinct values)