1. Introduction
The except and INTERSECT operators allow you to compare the results of two or more SELECT statements and return values that are not duplicates.
2. Differences
The except operator returns all the distinct values returned by the query on the left of the except operator, but not the value returned by the query on the right.
Intersect returns all the distinct values returned by the query on the left and right of the Intersect operator.
3. Matters needing attention
(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 the except
(3). SELECT into must be the first query in a statement containing 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 query on the left
4. Example:
Copy Code code as follows:
--Create 3 tables, 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 all the distinct values in the col1 column of the TableA table that do not exist TableC table col1 columns
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 except functions
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 going to get the result
--A null value indicates an unknown value. A null value is different from a blank or 0 value. There are no 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 in-in comparison are returned to unknown.
--Using a null value in conjunction with in or not is an unexpected result.
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 the except
The operation steps are: First, the Intersect of the TableB and TableC, and the TableA operation 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 the 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
--order BY clause
The column name or alias in the--order by clause must refer to the column name returned by the left query
SELECT col1 from TableA
INTERSECT
SELECT col3 from TableC
ORDER BY col1
The results are as follows:
Col1
-----------
1
5
