[explains the]sql except and intersect operators (compared to the results of two or more SELECT statements and goes to distinct values)

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)