Sql 简明教程

SQL - EXCEPT

The SQL EXCEPT Operator

EXCEPT SQL 中的运算符用于从左操作数（查询）检索所有唯一记录，但右操作数（查询）的结果集中存在记录除外。

换句话说，该运算符将左侧查询的不同值与右侧查询的结果集进行比较。如果在右侧查询的结果集中找到左侧查询的值，就会将其从最终结果中排除。

为了更好地理解，请考虑下图所示记录的两张表 -

如果我们对上述两张表执行 EXCEPT 运算符以检索名称，它将仅显示不在第二张表记录中且为第一张表中的不同记录。

这里，“Dev”在两张表中都是常见的。因此，EXCEPT 运算符将消除它，并仅检索“Sara”和“Jay”作为输出。

Syntax

以下是 Microsoft SQL Server 中 EXCEPT 运算符的 SQL 语法 -

SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
[Conditions] //optional
EXCEPT
SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
[Conditions] //optional

两个 SELECT 语句中列的数目和顺序应该是相同的。

Example

首先，让我们使用以下查询创建一个名为 STUDENTS 的数据表−

CREATE TABLE STUDENTS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   SUBJECT VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   PRIMARY KEY(ID)
);

使用以下查询将一些值插入到表中：

INSERT INTO STUDENTS VALUES
(1, 'Naina', 'Maths', 24, 'Cricket'),
(2, 'Varun', 'Physics', 26, 'Football'),
(3, 'Dev', 'Maths', 23, 'Cricket'),
(4, 'Priya', 'Physics', 25, 'Cricket'),
(5, 'Aditya', 'Chemistry', 21, 'Cricket'),
(6, 'Kalyan', 'Maths', 30, 'Football'),
(7, 'Aditya', 'Chemistry', 21, 'Cricket'),
(8, 'Kalyan', 'Chemistry', 32, 'Cricket');

所生成的表如下所示：

NAME

SUBJECT

AGE

HOBBY

Naina

Mathematics

Cricket

Varun

Physics

Football

Dev

Mathematics

Cricket

Priya

Physics

Cricket

Aditya

Chemistry

Cricket

Kalyan

Mathematics

Football

Aditya

Chemistry

Cricket

Kalyan

Chemistry

Cricket

现在，让我们使用以下查询创建一个名为 STUDENTS_HOBBY 的另一个表：

CREATE TABLE STUDENTS_HOBBY(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   PRIMARY KEY(ID)
);

一旦创建了该表，让我们使用以下查询向该表中插入一些值：

INSERT INTO STUDENTS_HOBBY VALUES
(1, 'Vijay', 'Cricket', 18),
(2, 'Varun', 'Football', 26),
(3, 'Surya', 'Cricket', 19),
(4, 'Karthik', 'Cricket', 25),
(5, 'Sunny', 'Football', 26),
(6, 'Dev', 'Cricket', 23);

创建的表如下所示：

ID	NAME	HOBBY	AGE
1	Vijay	Cricket	18
2	Varun	Football	26
3	Surya	Cricket	19
4	Karthik	Cricket	25
5	Sunny	Football	26
6	Dev	Cricket	23

现在，让我们对上述两张表执行 except 运算符 -

SELECT NAME, HOBBY, AGE FROM STUDENTS
EXCEPT
SELECT NAME, HOBBY, AGE FROM STUDENTS_HOBBY;

Output

上述查询的输出如下所示：

NAME

HOBBY

AGE

Aditya

Cricket

Kalyan

Cricket

Kalyan

Football

Naina

Cricket

Priya

Cricket

EXCEPT with BETWEEN Operator

我们可以在 SQL 中使用 EXCEPT 运算符和 BETWEEN 运算符，以排除落在指定范围内的记录。

Example

在以下 SQL 查询中，我们正在从 STUDENTS 表中检索年龄在 20 至 30 岁之间的学生的记录，但不包括 ALSO 在 STUDENTS_HOBBY 表中年龄在 20 至 30 岁之间的学生 -

SELECT NAME, HOBBY, AGE
FROM STUDENTS
WHERE AGE BETWEEN 20 AND 30
EXCEPT
SELECT NAME, HOBBY, AGE
FROM STUDENTS_HOBBY
WHERE AGE BETWEEN 20 AND 30

Output

当我们执行程序查询时，将获得如下输出 -

NAME

HOBBY

AGE

Aditya

Cricket

Kalyan

Football

Naina

Cricket

Priya

Cricket

Except with IN Operator

IN 运算符用于根据指定值列表过滤结果集。我们还可以在 SQL 中使用 EXCEPT 运算符和 IN 运算符，以排除与指定列表中的值匹配的记录。

Example

这里，我们正在从 STUDENTS 表中检索爱好为 Cricket 的学生的记录，但不包括 ALSO 在 STUDENTS_HOBBY 表中爱好为 Cricket 的学生 -

SELECT NAME, HOBBY, AGE FROM STUDENTS
WHERE HOBBY IN('Cricket')
EXCEPT
SELECT NAME, HOBBY, AGE FROM STUDENTS_HOBBY
WHERE HOBBY IN('Cricket')

Output

以下是以上查询的输出：

NAME

HOBBY

AGE

Aditya

Cricket

Kalyan

Cricket

Naina

Cricket

Priya

Cricket

EXCEPT with LIKE Operator

LIKE 运算符用于对字符串执行模式匹配。EXCEPT 运算符也可与 SQL 中的 LIKE 运算符一起使用，以排除与指定模式匹配的行。

Example

在这里，我们从 HOBBY 列的值以“F”开头的 STUDENTS 表中检索记录，同时排除 STUDENTS_HOBBY 表中的类似行−

SELECT ID, NAME, HOBBY, AGE FROM STUDENTS
WHERE HOBBY LIKE 'F%'
EXCEPT
SELECT ID, NAME, HOBBY, AGE FROM STUDENTS_HOBBY
WHERE HOBBY LIKE 'F%';

Output

以上查询的输出如下所示：

NAME

HOBBY

AGE

Kalyan

Football